CODEWITHSUNDEEP
mysql
Stephan Lachner
Stephan Lachner

Reputation: 21

MySQL how to get the max through inner join

For a swimming school, parents pay tuition fees for their kids. On a monthly basis, I need to collect the payable fees from a table that holds open balances, but only if their child is marked as 'active'. Here is the catch: an open balance for an "inactive child" is also considered payable IF at least one other child in the same family is marked active.

I have the following MySQL query:

SELECT sum(b.amount) as amount, f.name, u.email
FROM balance b 
LEFT JOIN student s ON b.user_id = s.user_id
LEFT JOIN student_family sf ON s.user_id = sf.user_id 
LEFT JOIN family f ON sf.fam_id = f.fam_id 
LEFT JOIN user u ON u.user_id = s.user_id
WHERE b.status = 'open' AND s.active = 1
GROUP BY u.email

I currently check s.active=1 but this part needs to be adjusted in such as way that we check if there is at least one student in the same family that have active=1.

My attempt (that didn't work):

...
WHERE b.status = 'open' AND ( SELECT max(active) from student s2 LEFT JOIN student_family sf2 ON sf2.user_id = s2.user_id WHERE sf2.fam_id = sf.fam_id ) = 1

How to accomplish this query?

Upvotes: 2

Views: 42

Answers (2)

Jayvee
Jayvee

Reputation: 10873

Using the exists clause will resolve the issue:

SELECT sum(b.amount) as amount, f.name, u.email
FROM balance b 
LEFT JOIN student s ON b.user_id = s.user_id
LEFT JOIN student_family sf ON s.user_id = sf.user_id 
LEFT JOIN family f ON sf.fam_id = f.fam_id 
LEFT JOIN user u ON u.user_id = s.user_id
WHERE b.status = 'open' AND exists
(select 1 from student s1 
JOIN student_family sf1 ON s1.user_id = sf1.user_id 
JOIN family f ON sf1.fam_id = f1.fam_id 
where s1.active = 1 and f.fam_id=f1.fam_id)
GROUP BY u.email

Upvotes: 1

Andrew
Andrew

Reputation: 1833

You could use a IN clause in your WHERE to see if the student_family.user_id is in a family with at least one active student:

Something like:

SELECT 
    sum(b.amount) as amount, 
    f.name, 
    u.email
FROM 
    balance b 
    LEFT JOIN student s ON b.user_id = s.user_id
    LEFT JOIN student_family sf ON s.user_id = sf.user_id 
    LEFT JOIN family f ON sf.fam_id = f.fam_id 
    LEFT JOIN user u ON u.user_id = s.user_id
WHERE 
    b.status = 'open' 
    AND (sf.user_id IN (SELECT sf.user_id FROM student_family sf INNER JOIN student s ON sf.user_id = s.user_id WHERE s.active = 1 GROUP BY sf.user_id  ))
GROUP BY 
    u.email

Upvotes: 0

Related Questions