SQL Select Question

Question

I have the following SQL Query wich selects a result from 3 tables 'work', 'media' and 'work_media'

I want all the work with 1 of its associated media, the following SQL query does that. But it only return the work that actually has media linked with it and thats's not what I want, I also want the work returned that has no media attached to it. How can I accomplisch that?

SELECT work.id, work.title, work.file_name_thumb FROM work, media, media_work WHERE media_work.work_id = work.id AND media_work.media_id = media.id GROUP BY work.id ORDER BY work.id DESC

Answer 1

You need to use an OUTER JOIN.

Answer 2

Use a LEFT OUTER JOIN for this:

SELECT w.id, w.title, w.file_name_thumb 
FROM work w
LEFT OUTER JOIN media_work mw ON mw.work_id = w.id 
LEFT OUTER JOIN media m ON mw.media_id = m.id 
GROUP BY w.id 
ORDER BY w.id DESC

Answer 3

You should try something like this:

SELECT w.id, w.title, w.file_name_thumb
FROM work w
LEFT JOIN media_work mw ON mw.work_id = w.id
LEFT JOIN media m ON m.id = mw.media_id
GROUP BY w.id
ORDER BY w.id DESC;

Don't really know what you want exactly, if you wanna see all the work, even those without media this would work fine, else the first LEFT JOIN should be a RIGHT JOIN.

Answer 4

You should use OUTER JOIN:

SELECT work.id, work.title, work.file_name_thumb FROM work
LEFT OUTER JOIN media_work ON media_work.work_id = work.id
LEFT OUTER JOIN media ON media_work.media_id = media.id
GROUP BY work.id ORDER BY work.id DESC;

LEFT OUTER JOIN will include all rows from the table on its left even if there is no corresponding row in the right table. Be careful though, that the missing row will be filled with NULLs.