CODEWITHSUNDEEP
sqlsql-server
ex1t3
ex1t3

Reputation: 55

How to create a right select query from 3 tables (SUM)?

I have 3 tables:

+-----+---------+
|cl_id|  name   |
+-----+---------+
|   1 |   adaf  |
|   2 |   rich  | - clients
|   3 |   call  |
|   4 |   alen  |
|   5 | courney |
|   6 |  warren |
+-----+---------+

+-----+---------+
|cl_id|  data   |
+-----+---------+
|   1 |      13 |
|   2 |    1000 | - table1
|   5 |      0  |
|   6 |      0  |
+-----+---------+

+-----+---------+
|cl_id|  data   |
+-----+---------+
|   2 |    -355 | - table2
|   3 |  35     | 
|   3 |  10     | 
|   5 |     46  |
|   5 |     50  |
|   5 |     10  |
+-----+---------+

And I have to combine those three tables, so the result should be:

+-----+---------+--------+---------+
|cl_id| name    |data_tb1|data_tb2 |
+-----+---------+--------+---------+
|   1 |   adaf  |    13  |      0  |
|   2 |   rich  |   1000 |    -355 |
|   3 |   call  |     0  |      45 |
|   4 |   alen  |     0  |      0  |
|   5 |  courney|     0  |     106 |
|   6 |  warren |     0  |      0  |
+-----+---------+--------+---------+

It should output all clients and theirs SUM(data) from table1 and table2. clients goes one-to-more.

Thanks in advance

Upvotes: 0

Views: 65

Answers (2)

Radim Bača
Radim Bača

Reputation: 10701

Simply using LEFT JOIN and GROUP BY

SELECT c.cl_id,
       c.name,
       COALESCE(SUM(t1.data), 0) AS data_tb1,
       COALESCE(SUM(t2.data), 0) AS data_tb2
FROM clients c
     LEFT JOIN table1 t1 ON c.cl_id = t1.cl_id
     LEFT JOIN table2 t2 ON c.cl_id = t2.cl_id
GROUP BY c.cl_id,
         c.name
ORDER BY c.cl_id;

Upvotes: 4

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If you are using SQL Server then simple Use Left Join as below :

SELECT C.cl_id,
       C.name,
       SUM(ISNULL(T.data, 0)) data_tb1,
       SUM(ISNULL(T1.data, 0)) data_tb2
FROM
(
    SELECT *
    FROM clients
) C
LEFT JOIN table1 T ON T.cl_id = C.cl_id
LEFT JOIN table2 T1 ON T1.cl_id = C.cl_id
GROUP BY C.cl_id,
         C.name
ORDER BY C.cl_id;

Desired Output :

+-----+---------+--------+---------+
|cl_id| name    |data_tb1|data_tb2 |
+-----+---------+--------+---------+
|   1 |   adaf  |    13  |      0  |
|   2 |   rich  |   1000 |    -355 |
|   3 |   call  |     0  |      45 |
|   4 |   alen  |     0  |      0  |
|   5 |  courney|     0  |     106 |
|   6 |  warren |     0  |      0  |
+-----+---------+--------+---------+

Upvotes: 0

Related Questions