[SOLVED] Need help with complex query - Lots of joins

[pulpfiction]

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.

[pulpfiction]

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;