hoque hoque - 1 month ago 8
SQL Question

Mysql join 3 tables query

I have 3 tables like following:

branch
id name
---------
1 abc
2 xyz

users
id branch_id name
-----------------
1 1 aa
2 1 bb
3 2 cc
4 1 dd
5 2 ee

sales

id user_id product price
1 1 xxxx 10
2 1 yyyy 20
3 2 zzzz 18
4 3 aaaa 12
5 2 bbbb 10
6 4 cccc 20


Now I want to get the total selling amount branch wise like:

branch_id total_price
---------------------
1 78
2 12


For that i write a sql query like:

SELECT SUM(s.price) , b.id
FROM sales s
JOIN branch b
GROUP BY id
HAVING s.user_id
IN (
SELECT id
FROM users
WHERE branch_id = b.id
)


But this does not provide the answer that I want. Please help me.

Answer

I think this should do the trick:

SELECT  branch.id AS branch_id, SUM(s.price) AS total_price
FROM branch
JOIN users  ON branch.id = users.branch_id
JOIN sales ON users.id = sales.user_id
GROUP BY branch.id;

Also you could use INNER JOIN instead of JOIN(Both are doing the same thing). With INNER JOIN it is possibly easier to read, especially your query contains other types of JOIN's like LEFT JOIN or RIGHT JOIN

Hope that helps!