Mysql Select Where Not Exists

Question

I have the following tables:

table name:: student

studentid     studentname
1001          Charlie Sheen
1002          John Cryer

table name:: studentpayment1

paymentid  studentid    fee_month  fee_year  totalamount
  1234          1001     February    2012        $500
  4321          1002     January     2012       $1500

table name:: studentpayment2

 id          pid        fee_type        fee_amount
 1          1234        Monthly Fee           $500
 2          4321        Exam Fee             $1500

Now, I am trying to find out the name of the students who hasn't paid the "Monthly Fee" for the month of February - year 2012.

I tried this following code but it doesn't generate the correct information.

$year="2012"; $month="February"; $fee_type="Monthly Fee";


SELECT DISTINCT studentid,
                studentname
FROM   student
WHERE  NOT EXISTS (SELECT   *
                   FROM     studentpayment1
                     JOIN   studentpayment2
                     ON     studentpayment1.paymentid = studentpayment2.pid
                   WHERE    fee_month = '$month'
                     AND    fee_type = '$fee_type'
                     AND    fee_year = '$year'
                     AND    student.studentid = studentpayment1.studentid)
LIMIT  $perPage

How do I fix this query?

@black_belt Try this query: `SELECT studentname FROM studentname, studentpayment1, studentpayment2 WHERE student.studentid = studentpayment1.studentid AND pid = paymentid AND fee_type != "Monthly Fee" AND fee_year = "2012" AND fee_month ="February"` — SIFE, Feb 20 '12 at 17:56

Ray · Answer 1 · 2012-02-20T18:18:08.633

This will give you all the students who paid nothing ever, nothing in February 2012, or no Monthly fees in Feb, 2012

SELECT * FROM student s 
   LEFT JOIN studentpayment1 sp1 
      ON s.studentid = sp1.studentid 
        AND ((sp1.fee_month = 'February' AND sp1.fee_year = '2012') OR sp1.fee_month is null)
   LEFT JOIN studentpayment2 sp2 
      ON sp1.paymentid = sp2.pid AND ( sp2.fee_type  = 'Monthly Fee' OR  sp2.fee_type is null)
   WHERE sp1.fee_month is null or sp2.fee_type is null

good luck

BTW here is some good schema advice: change all the primary keys on the tab to just 'id' and be consistant with you naming of foriegn keys: the primay key to the student table should be just 'id' in the studentpayment1 table it should have a column called student_id. The studentpayment2 table should have a column pointing to the studentpayment1 called studentpayment1_id not 'pid'.

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

Your variables should not be within the single quotes:

"WHERE    fee_month = '".$month."' "

Even better would be to bind your variables.

Discussion on binding here: How to bind SQL variables in Php?

edited May 23 '17 at 11:51

Community

1
1

answered Feb 20 '12 at 17:54

BD.

880
5
10

The OP may be in need of an example of binding variables. – Matt Fenwick Feb 20 '12 at 17:56

score 0 · Answer 3 · edited Feb 20 '12 at 18:02

I haven't tried running this query in real time, but this should help you out.

select distinct studentid from student where studentid NOT EXISTS (select studentid from studentpayment1 where  fee_month = '$month'
                     AND    fee_type = '$fee_type'
                     AND    fee_year = '$year')

or

select distinct studentid from student where NOT EXISTS (select studentid from studentpayment1 where  fee_month = '$month'
                     AND    fee_type = '$fee_type'
                     AND    fee_year = '$year')

score 0 · Answer 4 · answered Feb 20 '12 at 17:56

0

In case your subquery is correct (I haven't look at it) query bellow should work

SELECT DISTINCT studentid,studentname FROM student WHERE
studentid NOT IN (SELECT studentid FROM studentpayment1 JOIN studentpayment2 ON studentpayment1.paymentid=studentpayment2.pid WHERE 
fee_month='$month' AND fee_type='$fee_type' AND fee_year='$year' AND student.studentid=studentpayment1.studentid ) LIMIT $perPage

answered Feb 20 '12 at 17:56

Ivan

2,262
1
18
16

Thanks for your reply. I tried all the solutions posted here, but none of them worked. But after working on it 12 hours straight I figured out that my db query was okay I had some problems with some codes above the mysql query. I am sorry for creating this post. Should I delete this post? – black_belt Feb 21 '12 at 05:56

score 0 · Answer 5 · answered Feb 20 '12 at 18:00

Looks like this will do the thing:

$year="2012"; $month="February"; $fee_type="Monthly Fee";

$students_with_debt = "SELECT name FROM student WHERE studentid NOT IN (SELECT studentid FROM studentpayment1 p1 INNER JOIN studentpayment2 p2 ON p1.paymentid = p2.pid WHERE fee_month = '$month' AND p1.fee_year = '$year' AND p2.fee_type = '$fee_type');"

score -1 · Answer 6 · answered Feb 20 '12 at 17:56

Try using LEFT JOINs instead:

SELECT student.studentid, student.studentname FROM student
LEFT JOIN studentpayment1 ON studentpayment1.studentid = student.studentid
  AND studentpayment1.fee_month = ?
  AND studentpayment1.fee_year  = ?
LEFT JOIN studentpayment2 ON studentpayment2.pid = studentpayment1.paymentid
  AND studentpayment2.fee_type = ?
WHERE studentpayment2.id IS NULL

Make sure to put indexes on fee_month, fee_year, and fee_type, and replace all ? with your variables.

The OP might appreciate an explanation of why this approach works where the original failed. And what the benefits/drawback of adding indexes are. — Matt Fenwick, Feb 20 '12 at 18:00

Mysql Select Where Not Exists

6 Answers6