Jump to content

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


pulpfiction

Recommended Posts

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.

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.