LEFT JOIN returning duplicate rows
Posted: Sat Sep 27, 2014 6:08 pm
This is a query that returns the students registered for a course, and identifies those who are marked as "arrived" based on their accommodation (room at the retreat). However, some students may have more than one accommodation (because they are going to move from one room to another during their stay). In my left join query, the query correctly returns a row if there is no match on the right side, but it returns multiple rows if there is more than one match on the right side. DISTINCT and GROUP BY can't be used on the right side. What I need is for the query to return one row for a match on the left side, regardless of if or how many matches there are on the right side.
Thanks for any help.
SELECT A.student_id, A.invoice_date, A.invoice_set, A.last_name, A.first_name, A.addon_last_name, A.addon_first_name, A.gender, B.arrived, B.invoice_id AS arrived_invoice_id, A.invoice_id FROM (SELECT * FROM students.student_id, invoice_date, invoice_set, invoice_id, last_name, first_name, addon_last_name, addon_first_name, invoice.gender FROM invoice, students WHERE product_id = '$course_id' AND product_type = 'COURSE' AND commit_invoice='1' AND students.student_id=invoice.student_id)A LEFT JOIN invoice AS B ON A.student_id=B.student_id AND A.invoice_set=B.invoice_set AND ((A.addon_last_name=B.addon_last_name AND A.addon_first_name=B.addon_first_name) OR (A.addon_last_name IS NULL AND B.addon_last_name IS NULL)) AND B.product_type='LODGING' AND B.commit_invoice='1' ORDER BY A.invoice_date DESC
Thanks for any help.
SELECT A.student_id, A.invoice_date, A.invoice_set, A.last_name, A.first_name, A.addon_last_name, A.addon_first_name, A.gender, B.arrived, B.invoice_id AS arrived_invoice_id, A.invoice_id FROM (SELECT * FROM students.student_id, invoice_date, invoice_set, invoice_id, last_name, first_name, addon_last_name, addon_first_name, invoice.gender FROM invoice, students WHERE product_id = '$course_id' AND product_type = 'COURSE' AND commit_invoice='1' AND students.student_id=invoice.student_id)A LEFT JOIN invoice AS B ON A.student_id=B.student_id AND A.invoice_set=B.invoice_set AND ((A.addon_last_name=B.addon_last_name AND A.addon_first_name=B.addon_first_name) OR (A.addon_last_name IS NULL AND B.addon_last_name IS NULL)) AND B.product_type='LODGING' AND B.commit_invoice='1' ORDER BY A.invoice_date DESC