CODEWITHSUNDEEP
mysql
maxime schoeni
maxime schoeni

Reputation: 2857

How to solve this specific SQL query?

I am stuck with an this SQL problem: I need to find all members who did not pay their annual fees for 2014. Here is a sample of the database:

Table 'members'

| ID | name   |
---------------
| 1  | Franck | 
| 2  | Andy   |
| 3  | Jack   |


Table 'payements'

| ID  | memberID | year   | amount |
------------------------------------
| 1   | 1        | 2013   | 100    |
| 2   | 1        | 2014   | 100    |
| 3   | 2        | 2013   | 100    |

And I tried something like this:

SELECT members.name FROM members
LEFT JOIN payements ON (payements.memberID = members.ID)
WHERE (payements.year = 2014 AND payements.amount < 100) OR payements.memberID IS NULL

My query correctly finds Jack (who did never pay anything) but fails to find Andy because an entry exists for another year. How can I ask for all members who have no entry specifically for 2014 (or an entry with an amount below 100)?

Upvotes: 1

Views: 81

Answers (3)

xQbert
xQbert

Reputation: 35323

Consider this data in terms of sets

  1. Set 1 everyone who should have paid
  2. Set 2 people who is paid up correctly

We join the sets together as a left join excluding those who have paid in 2014 from the rest, we add the limits to the join so that only payments for current year in full are listed. we then exclude those from the complete set of users..

Select m.name, p.memberid, p.year, p.amount
from members m
LEFT JOIN payements p
 on m.id = p.memberId
 and (p.year = 2014  and p.amount >= 100)
WHERE p.year is null

The reason why your's didn't work was because the where clause was making the outer join an inner join. AND because you wanted a set of users who haven't paid. Not the set who has paid. So we needed to setup the second set as those who have paid... changing < to >=.

Upvotes: 2

terary
terary

Reputation: 1098

SELECT * FROM members WHERE ID NOT IN(SELECT memberID FROM payments WHERE year='2014')

Upvotes: 0

JosEduSol
JosEduSol

Reputation: 5436

Another way using sub-querys in WHERE.

In the sub-query you find all members who DID pay their annual fees. So in the outer query you keep only the members not inside the sub-query, those are the ones you want.

SELECT name 
FROM members 
WHERE ID NOT IN (SELECT memberID
                 FROM payements 
                 WHERE year = 2014 AND amount < 100)

BTW, do you mean amount <= 100 ?...

EDIT: For members who paid their fees in 2014, the amount must be greater or equal than 100, so here is a corrected version:

SELECT name 
FROM members 
WHERE ID NOT IN (SELECT memberID
                 FROM payements 
                 WHERE year = 2014 AND amount >= 100)

Added a new member "Amy" in your test, who only paid an amount of 80 in 2014, she is listed with Andy and Jack:

Andy

Jack

Amy

SQL FIDDLE

Upvotes: 1

Related Questions