CODEWITHSUNDEEP
mysqlsql
Mostafa Talebi
Mostafa Talebi

Reputation: 9183

My MySQL query with two JOINs does not work

I have written the following query, which does not work. I want to know how to make it work. It is a two-JOIN query which fails to work.

SELECT oc_download.download_id, oc_product_to_download.download_id, oc_download_description.download_id
  FROM oc_download 
         LEFT JOIN oc_product_to_download
                ON oc_download.download_id = oc_product_to_download.download_id 
         LEFT JOIN
                   oc_download.download_id = oc_download_description.download_id
 WHERE oc_product_to_download.product_id = 89

With single JOIN it works, but adding the second JOIN it fails. here is the clean working one-JOIN query:

SELECT oc_download.download_id, oc_product_to_download.download_id, oc_download_description.download_id
  FROM oc_download 
         LEFT JOIN oc_product_to_download
                ON oc_download.download_id = oc_product_to_download.download_id
 WHERE oc_product_to_download.product_id = 89

How should I use multiple JOIN in one single query?

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

This is your query fixed up a bit, using table aliases and proper join syntax:

SELECT od.download_id, opd.download_id, odd.download_id
FROM oc_download od LEFT JOIN
     oc_product_to_download opd
     ON od.download_id = opd.download_id LEFT JOIN
     oc_download_description odd
     od.download_id = odd.download_id
WHERE opd.product_id = 89;

You are using left join, but this appears to be unnecessary. The on clause is undoing the first outer join, turning it into an inner join (unmatched rows would have a NULL value, which are filtered out by the where clause). In fact, I would guess that your data has well defined foreign key relationships among the columns being joined. If this is the case, you should use inner join (or just join) for the query:

SELECT od.download_id, opd.download_id, odd.download_id
FROM oc_download od JOIN
     oc_product_to_download opd
     ON od.download_id = opd.download_id JOIN
     oc_download_description odd
     od.download_id = odd.download_id
WHERE opd.product_id = 89;

The left join is misleading because it implies that some keys might not match. You also run the risk of confusing the optimizer.

Upvotes: 2

juergen d
juergen d

Reputation: 204766

You forgot the table name in the 2nd join

SELECT d.download_id, p.download_id, dd.download_id 
FROM oc_download d
LEFT JOIN oc_product_to_download p ON d.download_id = p.download_id 
LEFT JOIN oc_download_description dd ON d.download_id = dd.download_id 
WHERE p.product_id = 89

And your where clause turns your left join into an inner join. If you don't want that then change your query to

SELECT d.download_id, p.download_id, dd.download_id 
FROM oc_download d
LEFT JOIN oc_product_to_download p ON d.download_id = p.download_id 
                                  AND p.product_id = 89
LEFT JOIN oc_download_description dd ON d.download_id = dd.download_id

Upvotes: 2

Related Questions