I'm struggling to compile a query for the following and wonder if anyone can please help (I'm a SQL newbie).
I have two tables:
student_details, which contains the columns:
student_id (PK), firstname, surname (and others, but not relevant to this query)
membership_fee_payments, which contains details of monthly membership payments for each student and contains the columns:
membership_fee_payments_id (PK), student_id (FK), payment_month, payment_year, amount_paid
I need to create the following query:
The query could be for any month/year, March is just an example. I want to return in the query
firstname, surname from
I can query successfully who has paid for a certain month and year, but I can't work out how to query who has not paid!
Here is my query for finding out who has paid:
SELECT student_details.firstname, student_details.surname,
INNER JOIN membership_fee_payments
ON student_details.student_id = membership_fee_payments.student_id
WHERE membership_fee_payments.payment_month = "March"
AND membership_fee_payments.payment_year = "2012"
ORDER BY student_details.firstname
I have tried a left join and left outer join but get the same result. I think perhaps I need to use NOT EXISTS or IS NULL but I haven't had much luck writing the right query yet.
Any help much appreciated.
I'm partial to using
WHERE NOT EXISTS Typically that would look something like this
SELECT D.firstname, D.surname FROM student_details D WHERE NOT EXISTS (SELECT * FROM membership_fee_payments P WHERE P.student_id = D.student_id AND P.payment_year = '2012' AND P.payment_month = 'March' )
This is know an a correlated subquery as it contains references to the outer query. This allows you to include your join criteria in the subquery without necessarily writing a JOIN. Also, most RDBMS query optimizers will implement this as a SEMI JOIN which does not typically do as much 'work' as a complete join.
You could use a
left join. When the payment is missing, all the columns in the
left join table will be
SELECT student_details.firstname, student_details.surname, FROM student_details LEFT JOIN membership_fee_payments ON student_details.student_id = membership_fee_payments.student_id AND membership_fee_payments.payment_month = "March" AND membership_fee_payments.payment_year = "2012" WHERE membership_fee_payments.student_id is null ORDER BY student_details.firstname
You can also write following query. This will gives your expected output.
SELECT student_details.firstname, student_details.surname, FROM student_details Where student_details.student_id Not in (SELECT membership_fee_payments.student_id from membership_fee_payments WHERE membership_fee_payments.payment_year = '2012' AND membership_fee_payments.payment_month = 'March' )