CODEWITHSUNDEEP
mysqljoin
Matt Leyland
Matt Leyland

Reputation: 2329

MySQL JOIN two lookups

I have two tables

products

id
code
description
category
subcategory

product_categorys

id
category_name
category_master

Data in products table

1, UK001, Description Text, 1, 2

Data in product_category table

1, Network Cable,  0
2, CAT6, 1

I want to lookup the products and return the category and subcategory i have reserached the best way to do this butt canot get my head around it, i have managed to return one lookup using the following.

SELECT products.product_code, products.description, product_category.category_name, product_category.category_name
FROM products
LEFT JOIN product_category ON product_category.id = products.category AND product_category.ID = products.subcategory
ORDER BY description ASC

Which returns as expected 

UK001, Description Text, Network Cables

I also want to return the subcategory, adding another join does not work and this is where i am stuck,

Upvotes: 1

Views: 118

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You just need two joins:

SELECT p.product_code, p.description, pc.category_name, pcs.category_name
FROM products p LEFT JOIN
     product_category pc
     ON pc.id = p.category LEFT JOIN
     product_category pcs
     ON pcs.ID = p.subcategory
ORDER BY description ASC;

Note also the use of table aliases. These make the query easier to write and to read.

Upvotes: 3

Related Questions