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
your “A” subquery begins …SELECT * FROM
students.student_id, invoice_date, invoice_set,
as to your question, which one would you like? you have to be able to identify the row from the right table that you want returned, based on some value in some column(s)
Thank you. Yes, I didn’t see that I had two from clauses in the query I posted before (don’t know when that snuck in there), but the issue hasn’t changed.
you have to be able to identify the row from the right table that you want returned
In most cases the query would return one row on the right to one row on the left. In situations where it returns more than one on the right, I don’t care which one is returned, so what I really want to do is apply a LIMIT 1 to the right side, but when I have tried adding LIMIT 1, it applies it to left and right, returning a total of one rows, which is not what I want. There is nothing I can apply GROUP BY to on the right side. So really, the solution I am hoping for is a way to add a LIMIT 1 to the right side.
Thanks
$sql=$dbh->prepare("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 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 ");
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 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 OUTER
JOIN ( SELECT student_id
, invoice_set
, addon_last_name
, addon_first_name
, MAX(invoice_id) AS latest
FROM invoice
WHERE product_type='LODGING'
AND commit_invoice='1'
GROUP
BY student_id
, invoice_set
, addon_last_name
, addon_first_name ) AS M
ON M.student_id = A.student_id
AND M.invoice_set = A.invoice_set
AND (
( M.addon_last_name = A.addon_last_name
AND M.addon_first_name = A.addon_first_name
)
OR ( M.addon_last_name IS NULL
AND A.addon_last_name IS NULL
)
)
LEFT OUTER
JOIN invoice AS B
ON B.invoice_id = M.latest
ORDER
BY A.invoice_date DESC
