I need a 3 table join-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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`;