试图显示来自两个表的数据，而不是EXESTS

Question

I have this query, which works:

SELECT DISTINCT sid, name, last_name, tuition
FROM students 
WHERE EXISTS (SELECT * FROM payments 
WHERE payments.forMonth = 'May'
AND students.sid = payments.sid)

However, when I try to add a couple columns from the payments table, the query fails:

SELECT DISTINCT students.sid, students.name, students.last_name,
students.tuition, payments.amount, payments.forMonth
FROM students payments
WHERE EXISTS (
SELECT * 
FROM payments
WHERE payments.forMonth =  'Apr'
AND students.sid = payments.sid)

I've tried this with an inner join in the top-half of the query, but that generated wrong results. Is there a way to do this?

(The reason for the EXISTS condition is that I'll have a toggle on the filter form that will show either payments made or payments not made.)

Answer 1

The outer query uses an inner join and passes its forMonth information to the subquery. Think of it this way, how could the exist statement be useful, if it does not know which month the outer query is looking at.

SELECT DISTINCT students.sid, students.name, students.last_name,
students.tuition, payments.amount, payments.forMonth
FROM students
 INNER JOIN payments ON students.sid = payments.sid
WHERE EXISTS (
SELECT * 
FROM payments p2 
WHERE p2.forMonth =  payments.forMonth )
And payments.forMonth = 'Apr'