Yunus Einsteinium Yunus Einsteinium - 1 year ago 79
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
(SELECT SUM(amount) AS totalCollection, member_id
FROM collections
GROUP BY member_id) AS memberCollections
ON memberCollections.member_id =

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

(SELECT SUM(amount) AS totalDeduction, member_id
FROM deductions
GROUP BY member_id) AS memberDeductions
ON memberDeductions.member_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
no result is returned at all.

Answer Source

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, SUM(c.amount) AS totalCollection, SUM(cpt.amount) AS totalStore, SUM(d.amount) AS totalDeduction
FROM members m
LEFT JOIN collections c ON = c.member_id
LEFT JOIN credit_payment_transaction cpt ON = cpt.member_id
LEFT JOIN deductions d ON = d.member_id
HAVING SUM(c.amount) > 0 OR  SUM(cpt.amount) > 0 OR SUM(d.amount) > 0

Also this query will eliminate the member duplications

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download