MYSQL GET ALL PRODUCT FROM PARENT CATEGORY

Question

I HAVE TABLE

TABLE CATEGORY
id | name | parent
1 | gadget | 0
2 | phone | 1
3 | laptop | 1
....

TABLE PRODUCT
id | name | cat
1 | post 1 | 2
2 | post 2 | 2
3 | post 3 | 3
....

i want to get product by category and subcategory

Display all product in gadget category

How i can do that ?

score 1 · Accepted Answer · answered Dec 16 '19 at 15:31

1

With just one level of parent/child relationship (as shown in your sample data), you can self-join the category table:

select p.*, c.name, c1.name
from product p
inner join category c on c.id = p.cat
left join category c1 on c1.id = c.parent

answered Dec 16 '19 at 15:31

GMB

216,147
25
84
135

How about if i just have the value of category name only. – wayan wahyu Adi Putra Dec 16 '19 at 15:46
@wayanwahyuAdiPutra: if you want only the products in the `'gadget'` category, you can add a `where` clause at the end of the query: `where c.name = 'gadget' or c1.name = 'gadget'` – GMB Dec 16 '19 at 15:49
I want all product from category and subcategory. Is that way same? – wayan wahyu Adi Putra Dec 16 '19 at 15:51
@wayanwahyuAdiPutra: this where clause gives you products whose category or subcategory is `'gadget'`. – GMB Dec 16 '19 at 15:52

Habibillah · Answer 2 · 2019-12-16T15:57:07.630

0

the simple way, use double query

SELECT A.* FROM PRODUCT A
INNER JOIN CATEGORY B ON A.CAT = B.ID
WHERE B.ID = (SELECT ID FROM CATEGORY WHERE NAME = 'gadget') 
OR B.PARENT= (SELECT ID FROM CATEGORY WHERE NAME = 'gadget')

edited Dec 16 '19 at 15:57

answered Dec 16 '19 at 15:34

Habibillah

27,347
5
36
56

In case i just have value of category name only. Like 'gadget' what should i do? – wayan wahyu Adi Putra Dec 16 '19 at 15:48
use double query – Habibillah Dec 16 '19 at 15:57

MYSQL GET ALL PRODUCT FROM PARENT CATEGORY

2 Answers2