CODEWITHSUNDEEP
mysqlsqlselectjoinsum
Joel James
Joel James

Reputation: 1345

MySQL join query with multiple where condition

I have 3 tables. I need to join those 3 and get 2 fields from each table. And there will be few where conditions. Where condition is for date range. Even if one table has the the result I need to show it along with other table data showing as 0. I have tried using inner join. But what it does is taking only 1st where condition and if no result in first it will not go for next conditions. My table structures and required output are shown below.

table1

+--------+---------+------------+
| amount | site_id |    date    |
+--------+---------+------------+
|     10 |       1 | 12/12/2014 |
|     50 |       2 | 10/12/2014 |
|     30 |       3 | 05/11/2014 |
+--------+---------+------------+

table2

+--------+---------+------------+
| amount | site_id |    date    |
+--------+---------+------------+
|    100 |       1 | 2/11/2014  |
|     40 |       2 | 10/10/2014 |
|     30 |       3 | 05/11/2014 |
+--------+---------+------------+

table3

+--------+---------+------------+
| amount | site_id |    date    |
+--------+---------+------------+
|     60 |       1 | 12/12/2014 |
|     50 |       3 | 11/12/2014 |
|     70 |       4 | 05/09/2014 |
+--------+---------+------------+

output : total amounts between 01/12/2014 and 31/12/2014

+---------+---------------+---------------+---------------+-------+
| site_id | table1_amount | table2_amount | table3_amount | total |
+---------+---------------+---------------+---------------+-------+
|      1 |            60 |             0 |           60   |    120|
|      3 |            0  |             0 |           50   |    50 |
+---------+---------------+---------------+---------------+-------+

Can anyone suggest a query to get this output? This is what I have done so far

select sum(table1.amount),sum(table2.amount),sum(table3.amount),(sum(table1.amount)+sum(table2.amount)+sum(table3.amount)) from table1 inner join table2 on table1.site_id=table2.site_id inner join table3 on table3.site_id=table2.site_id where table1.date>='01/12/2014' and table1.date<='31/12/2014' or table2.date>='01/12/2014' and table2.date<='31/12/2014' or table3.date>='01/12/2014' and table3.date<='31/12/2014' group by table1.site_id

Upvotes: 1

Views: 216

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT S.site_id, 
       IFNULL(t1.table1_Amount, 0) AS table1_Amount, 
       IFNULL(t2.table2_Amount, 0) AS table2_Amount, 
       IFNULL(t3.table3_Amount, 0) AS table3_Amount, 
      (IFNULL(t1.table1_Amount, 0) + IFNULL(t2.table2_Amount, 0) + IFNULL(t3.table3_Amount, 0)) AS total 
FROM Site S
LEFT OUTER JOIN ( SELECT t1.site_id, SUM(t1.amount) AS table1_Amount 
                  FROM table1 t1
                  WHERE t1.date >= '01/12/2014' AND t1.date <= '31/12/2014'
                  GROUP BY t1.site_id
                ) AS t1 ON S.site_id = t1.site_id
LEFT OUTER JOIN ( SELECT t2.site_id, SUM(t2.amount) AS table2_Amount 
                  FROM table2 t2
                  WHERE t2.date >= '01/12/2014' AND t2.date <= '31/12/2014'
                  GROUP BY t2.site_id
                ) AS t2 ON S.site_id = t2.site_id
LEFT OUTER JOIN ( SELECT t3.site_id, SUM(t3.amount) AS table3_Amount 
                  FROM table1 t3
                  WHERE t3.date >= '01/12/2014' AND t3.date <= '31/12/2014'
                  GROUP BY t3.site_id
                ) AS t3 ON S.site_id = t3.site_id;

Upvotes: 1

Related Questions