CODEWITHSUNDEEP
sql
jnhamilton
jnhamilton

Reputation: 123

SQL Query for finding values that do not exist in one table, with WHERE clause

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:

(1) student_details, which contains the columns: student_id (PK), firstname, surname (and others, but not relevant to this query)

(2) 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 student_details.

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, 
FROM student_details
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.

Upvotes: 2

Views: 7036

Answers (3)

Atul Patel
Atul Patel

Reputation: 163

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' )

Upvotes: 0

Andomar
Andomar

Reputation: 238086

You could use a left join. When the payment is missing, all the columns in the left join table will be null:

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

Upvotes: 2

Code Magician
Code Magician

Reputation: 23972

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.

Upvotes: 7

Related Questions