Yunus Einsteinium Yunus Einsteinium - 4 months ago 12
MySQL Question

Join with subquery

I have 4 tables

1. members(id, name, milk_no, ...)
2. collections(id, member_id, amount, date, ...)
3. credit_payment_transaction(id, member_id, amount, date, ...)
4. deductions(id, member_id, amount, date, ...)


I am trying to come up with a query that will return for each member.

milk_no | totalDeduction | totalStore | totalCollection


The result should return only member with atleast one of
totalDeduction | totalStore | totalCollection


This is what i have come up with

SELECT members.milk_no, memberCollections.totalCollection, stores.totalStore, memberDeductions.totalDeduction
FROM members
LEFT JOIN
(SELECT SUM(amount) AS totalCollection, member_id
FROM collections
GROUP BY member_id) AS memberCollections
ON memberCollections.member_id = members.id

LEFT JOIN
(SELECT SUM(amount) AS totalStore, member_id
FROM credit_payment_transaction
GROUP BY member_id) AS stores
ON stores.member_id = members.id

LEFT JOIN
(SELECT SUM(amount) AS totalDeduction, member_id
FROM deductions
GROUP BY member_id) AS memberDeductions
ON memberDeductions.member_id = members.id


The above query return this

enter image description here

The problem with this result is, it includes unwanted data(the ones with 3 nulls). When i change to
RIGHT JOIN
no result is returned at all.

Answer

Simply add a WHERE clause at the bottom of your query:

WHERE totalCollection IS NOT NULL OR totalStore IS NOT NULL OR totalDeduction IS NOT NULL

You can simplify your query like this:

SELECT m.id, SUM(c.amount) AS totalCollection, SUM(cpt.amount) AS totalStore, SUM(d.amount) AS totalDeduction
FROM members m
LEFT JOIN collections c ON m.id = c.member_id
LEFT JOIN credit_payment_transaction cpt ON m.id = cpt.member_id
LEFT JOIN deductions d ON m.id = d.member_id
GROUP BY m.id
HAVING SUM(c.amount) > 0 OR  SUM(cpt.amount) > 0 OR SUM(d.amount) > 0

Also this query will eliminate the member duplications

Comments