Question 90019 in PostgreSQL Question posted in
The official documentation can be found here.

I need a 3 table join – Postgresql

This is my parent table acc_detial –

ACC_DETIAL example –

acc_id
1
2
3

Now i have 3 tables:

  • ORDER
  • EMAIL
  • REPORT

Each table contains 100 rows and acc_id are ForeignKey from ACC_DETIAL.

In ORDER table I have a columns ACC_ID and QUANTITY. I want the count of ACC_ID and sum of QUANTITY.

ORDER table example:

acc_id quantity date
1 2 2022/01/22
2 5 2022/01/23
1 10 2022/01/25
3 1 2022/01/25

In EMAIL table I have a column name ACC_ID and I want count of ACC_ID.

EMAIL table example:

acc_id mail date
1 5 2022/01/22
2 10 2022/01/22
1 7 2022/01/23
1 7 2022/01/24
2 10 2022/01/25

In REPORT table I have a columns ACC_ID and TYPE and I want the count of ACC_ID and TYPE. Note that TYPE column has only two, possible values:

  • postive
  • negative

I want count of each, i.e. count of postive and count of negative in TYPE column.

REPORT table example:

acc_id type date
1 positive 2022/01/22
2 negative 2022/01/22
1 negative 2022/01/23
2 postitive 2022/01/26
2 postitive 2022/01/27

I need to take this in a single i need answer as raw query or sqlalchemy. Is it possible or not? Do I need to write separate query to get each table result ?

Result –

result based on above examplec –

acc_id total_Order_acc_id total_Order_quantity total_Email_acc_id total_Report_acc_id total_postitive_report total_negative_report
1 2 12 3 2 1 1
2 1 5 2 3 2 1
3 1 1 Null Null Null Null
Tags:

2

Answers


  1. 0

    Sample :

    Select
  `order`.`acc_id`,
  report_email_select.`type`,
  report_email_select.report_count,
  report_email_select.email_count,
  SUM(`quantity`) as quantity_sum
FROM
  `order`
  Left JOIN(
    Select
      report_select.`acc_id`,
      report_select.`type`,
      report_select.report_count,
      COUNT(*) as email_count
    from
      (
        SELECT
          report.`acc_id`,
          report.`type`,
          COUNT(*) as report_count
        FROM
          `report`
        WHERE
          1
        GROUP BY
          report.`acc_id`,
          report.`type`
      ) AS report_select
      INNER JOIN email ON email.acc_id = report_select.acc_id
    GROUP BY
      report_select.`acc_id`,
      report_select.`type`
  ) AS report_email_select ON `order`.acc_id = report_email_select.acc_id
GROUP BY
  `order`.`acc_id`,
  report_email_select.`type`;
    Login or Signup to reply.
  2. 0

    You need to aggregate then join as the following:

    SELECT ADL.acc_id,
       ORD.ord_cnt AS total_Order_acc_id,
       ORD.tot_quantity AS total_Order_quantity,
       EML.eml_cnt AS total_Email_acc_id,
       RPT.rpt_cnt AS total_Report_acc_id,
       RPT.pcnt AS total_postitive_report,
       RPT.ncnt AS total_negative_report
FROM ACC_DETIAL ADL LEFT JOIN 
(
  SELECT acc_id, 
         SUM(quantity) AS tot_quantity, 
         COUNT(*) AS ord_cnt 
  FROM ORDERS
  GROUP BY acc_id
) ORD
ON ADL.acc_id = ORD.acc_id
LEFT JOIN 
(
  SELECT acc_id, COUNT(*) AS eml_cnt
  FROM EMAIL
  GROUP BY acc_id
) EML
ON ADL.acc_id = EML.acc_id
LEFT JOIN 
(
  SELECT acc_id, 
         COUNT(*) AS rpt_cnt, 
         COUNT(*) FILTER (WHERE type='positive') AS pcnt,
         COUNT(*) FILTER (WHERE type='negative') AS ncnt
  FROM REPORT 
  GROUP BY acc_id
) RPT
ON ADL.acc_id = RPT.acc_id

    See demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search