pulpfiction Posted November 12, 2007 Share Posted November 12, 2007 Hello, I'm trying to get a list of all students and their last semester class details (it's just one class per semester), plus how many unpaid invoices each student has. The current query I have is: SELECT u.*, s.*, sem.*, lsem.*, COUNT(i.invoice) invoices_due FROM (SELECT MAX(IF(end_semester < NOW(), semester, null)) last_semester, MAX(IF(semester <= NOW(), semester, null)) this_semester, MIN(IF(semester > NOW(), semester, null)) next_semester FROM ssm_demo_semesters) sem CROSS JOIN ssm_demo_users u INNER JOIN ssm_demo_students s ON u.username = s.username INNER JOIN ssm_demo_registrations r ON u.username = r.username INNER JOIN ssm_demo_classes lsem ON r.class = lsem.class AND sem.last_semester = lsem.semester LEFT JOIN ssm_demo_invoice_recipients i ON u.username = i.username AND i.paid = 0 GROUP BY u.username ORDER BY last_name ASC LIMIT 0 , 20; The problem with this query is it's excluding the users who weren't on any class last semester. I tried adding some LEFT JOINs on those last 2 INNER JOINs but no sucess. I added some test data so anyone can try run the query locally: http://www.datazero.net/chico/test_data.sql And here's an example of what I expect to get and what I actually get from this test data: http://www.datazero.net/chico/expected_actual_results.txt So please, can anyone help? I've been stuck on this query for the last 2 days. Quote Link to comment Share on other sites More sharing options...
pulpfiction Posted November 12, 2007 Author Share Posted November 12, 2007 Well, after some brainstorm and a little help I finally got the query working exactly how I wanted. Here's the final version for the query: SELECT u.*, s.*, lsc.name lsc_name, tsc.name tsc_name, nsc.name nsc_name, CONCAT(lsci1.first_name, ' ', lsci1.last_name) lsc_instructor1_name, CONCAT(lsci2.first_name, ' ', lsci2.last_name) lsc_instructor2_name, CONCAT(tsci1.first_name, ' ', tsci1.last_name) tsc_instructor1_name, CONCAT(tsci2.first_name, ' ', tsci2.last_name) tsc_instructor2_name, CONCAT(nsci1.first_name, ' ', nsci1.last_name) nsc_instructor1_name, CONCAT(nsci2.first_name, ' ', nsci2.last_name) nsc_instructor2_name, COUNT(i.user_invoice) invoices_due FROM (SELECT MAX(IF(end_semester < NOW(), semester, null)) last_semester, MAX(IF(semester <= NOW(), semester, null)) this_semester, MIN(IF(semester > NOW(), semester, null)) next_semester FROM ssm_demo_semesters) sem CROSS JOIN ssm_demo_users u INNER JOIN ssm_demo_students s ON u.username = s.username LEFT JOIN ( ssm_demo_registrations rl INNER JOIN ssm_demo_classes lsc ON rl.class = lsc.class LEFT JOIN ssm_demo_users lsci1 ON lsc.instructor1 = lsci1.username LEFT JOIN ssm_demo_users lsci2 ON lsc.instructor2 = lsci2.username ) ON u.username = rl.username AND sem.last_semester = lsc.semester LEFT JOIN ( ssm_demo_registrations rt INNER JOIN ssm_demo_classes tsc ON rt.class = tsc.class LEFT JOIN ssm_demo_users tsci1 ON tsc.instructor1 = tsci1.username LEFT JOIN ssm_demo_users tsci2 ON tsc.instructor2 = tsci2.username ) ON u.username = rt.username AND sem.this_semester = tsc.semester LEFT JOIN ( ssm_demo_registrations rn INNER JOIN ssm_demo_classes nsc ON rn.class = nsc.class LEFT JOIN ssm_demo_users nsci1 ON nsc.instructor1 = nsci1.username LEFT JOIN ssm_demo_users nsci2 ON nsc.instructor2 = nsci2.username ) ON u.username = rn.username AND sem.next_semester = nsc.semester LEFT JOIN ssm_demo_invoice_recipients i ON u.username = i.username AND i.paid = 0 GROUP BY u.username ORDER BY last_name ASC LIMIT 0 , 20; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.