CODEWITHSUNDEEP
mysqljoinrelational-database
Smith Smithy
Smith Smithy

Reputation: 585

Select data from two tables. one table is parent/child and I need to get the parents without a relation

I have two tables

products

id  |  catID  |  UID
--------------------
1   |  3      |  3

categories

id  |  cat_name  |  parent
--------------------------
2   |  XYZ       |  0
3   |  abc       |  2

I need to pull each product and its related category AND that categories related parent based solely on the id of the product....

I have tried a couple of variations but can not quite get the parent categories into the fetch. There is no relationship for parent categories in the products table.

SELECT
  a.product_name, a.catID, b.cat_name, b.parent
FROM
  products a, categories b
WHERE
  a.id = '$_SESSION[spid]' 
  AND b.id = a.catID
  OR b.id = b.parent /// not correct..

Upvotes: 1

Views: 1537

Answers (1)

Trenton Trama
Trenton Trama

Reputation: 4930

select p.*,c1.cat_name as category, c2.cat_name as parent_category
from products p
left join categories c1 on (c1.id=p.catId)
left join categories c2 on (c1.parent=c2.id)

Upvotes: 3

Related Questions