Drew Drew - 1 month ago 6
MySQL Question

MySQL Multiple Joins in one query?

I have the following query:

SELECT
dashboard_data.headline,
dashboard_data.message,
dashboard_messages.image_id
FROM dashboard_data
INNER JOIN dashboard_messages
ON dashboard_message_id = dashboard_messages.id


So I am using an
INNER JOIN
and grabbing the
image_id
. So now, I want to take that image_id and turn it into
images.filename
from the images table.

How can I add that in to my query?

Answer

You can simply add another join like this:

SELECT dashboard_data.headline, dashboard_data.message, dashboard_messages.image_id, images.filename
FROM dashboard_data 
    INNER JOIN dashboard_messages 
        ON dashboard_message_id = dashboard_messages.id
    INNER JOIN images
        ON dashboard_messages.image_id = images.image_id 

However be aware that, because it is an INNER JOIN, if you have a message without an image, the entire row will be skipped. If this is a possibility, you may want to do a LEFT OUTER JOIN which will return all your dashboard messages and an image_filename only if one exists (otherwise you'll get a null)

SELECT dashboard_data.headline, dashboard_data.message, dashboard_messages.image_id, images.filename
FROM dashboard_data 
    INNER JOIN dashboard_messages 
        ON dashboard_message_id = dashboard_messages.id
    LEFT OUTER JOIN images
        ON dashboard_messages.image_id = images.image_id