当前位置: 动力学知识库 > 问答 > 编程问答 >

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:

  • which students have not paid fees for March 2012?

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.

网友答案:

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 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
网友答案:

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

分享给朋友:
您可能感兴趣的文章:
随机阅读: