mysql - 我想显示 2 个表中的类别、子类别、产品和价格

Question

我有2个表。其中一个是类别，其中包含id，cat_name，parent_id。该表包含类别和子类别。类别的parent_id为0，子类别的父id为类别的id。另一个表是product，包含id，pnname，price，pro_id。pro_id是子类别的id。

没有为该查询提供正确的值

  id cat_name parent_id      id  pname  pro_id
  --------------------      --------------------
   1 Electronics  0           1  abc     3
   2 Clothing     0           2  def     4
   2 Clothing     0
   3 Phone        1
   4 Kurti        2

  output:
  category      subcategory    Product 
  --------------------------------------
   Electronics     Phone       abc  
   Clothing        Kurti       def
  select product.pname from product where product.pro_id=(SELECT c1.cat_name, c2.cat_name FROM category c1, category c2 WHERE c1.id = c2.parent_id)

SELECT product.pname, category.cat_name
FROM product
INNER JOIN category ON category.id = product.pro_id
OR category.parent_id =0
 SELECT product.pname, category.cat_name
 FROM product INNER JOIN category ON product.pro_id=(SELECTc1.cat_name,  c2.cat_name FROM category c1, category c2 WHERE c1.id = c2.parent_id)

Answer 1

SELECT * FROM category c1
LEFT JOIN category c2
    ON c2.parent_id = c1.id
LEFT JOIN product p
    ON p.pro_id = c2.id;