MySQL join with a "bounce" off a third table

Question

I have 3 MySQL tables.

companies with company_id and company_name

products with product_id and company_id

names with product_id, product_name and other info about the product

I'm trying to output the product_name and the company_name in one query for a given product_id.

Basically I need information from the names and companies tables and the link between them is the products table.

How do I do a join that needs to "bounce" off a third table?

Something like this but this obviously doesn't work:

SELECT product_name, company_name
FROM names
LEFT OUTER JOIN companies ON 
    (names.product_id = products.product_id and products.company_id = companies.company_id)
WHERE product_id = '12345'

Answer 1

select n.product_name, c.company_name 
from names n
left outer join products p on n.product_id = p.product_id
left outer join companies c on p.company_id = c.company_id
where n.product_id = '12345' 

Answer 2

You nearly have it, you just need to include the third table as another join in your query:

SELECT product_name, company_name
FROM names
LEFT JOIN products ON names.product_id = products.product_id
LEFT JOIN companies ON products.company_id = companies.company_id
WHERE product_id = '12345'

Also you should note that if you are using LEFT JOIN then the company name could be NULL if the company that made the product is unknown. So you need to test for that in your code to avoid an exception. If you know that it should never be NULL, or if you want to explicilty exclude products for which you don't know the company then use an INNER JOIN instead of a LEFT JOIN in both cases.