I have used hierarchical data modal to store products in database. Products have only main and subcategory, and this is the result when I retrieving the Full Tree of Products.
我使用了分层数据模式在数据库中存储产品。产品只有主和子类别,这是我检索整个产品树的结果。
SELECT t1.name AS lev1,
t2.name as lev2,
t3.name as lev3,
FROM categories AS t1
LEFT JOIN categories AS t2
ON t2.parent = t1.category_id
LEFT JOIN categories AS t3
ON t3.parent = t2.category_id
WHERE t1.name = 'Products'
+----------+----------------------+-------------------+
| lev1 | lev2 | lev3 |
+----------+----------------------+-------------------+
| Products | Computers | Laptops |
| Products | Computers | Desktop Computers |
| Products | Computers | Tab PCs |
| Products | Computers | CRT Monitors |
| Products | Computers | LCD Monitors |
| Products | Computers | LED Monitors |
| Products | Mobile Phones | LG Phone |
| Products | Mobile Phones | Anroid Phone |
| Products | Mobile Phones | Windows Mobile |
| Products | Mobile Phones | iPad |
| Products | Mobile Phones | Samsung Galaxy |
| Products | Digital Cameras | test |
| Products | Printers and Toners | NULL |
| Products | test | abc |
| Products | test2 | NULL |
| Products | test3 | NULL |
| Products | Computer Accessaries | USB Cables |
| Products | Computer Accessaries | Network Cables |
+----------+----------------------+-------------------+
My question is I need to select level2 and level3 category id along with this select query.
我的问题是,我需要选择level2和level3类别id以及这个select查询。
I tried it something like this:
我试过了
SELECT t1.name AS lev1,
t2.name as lev2,
t3.name as lev3,
t3.category_id
FROM categories AS t1
LEFT JOIN categories AS t2
ON t2.parent = t1.category_id
LEFT JOIN categories AS t3
ON t3.parent = t2.category_id
WHERE t1.name = 'Products'
But its only provide level3 IDs. like this.
但它只提供三级id。像这样。
+----------+----------------------+-------------------+-------------+
| lev1 | lev2 | lev3 | category_id |
+----------+----------------------+-------------------+-------------+
| Products | Computers | Laptops | 3 |
| Products | Computers | Desktop Computers | 4 |
| Products | Computers | Tab PCs | 5 |
| Products | Computers | CRT Monitors | 6 |
| Products | Computers | LCD Monitors | 7 |
| Products | Computers | LED Monitors | 8 |
| Products | Mobile Phones | LG Phone | 10 |
| Products | Mobile Phones | Anroid Phone | 11 |
| Products | Mobile Phones | Windows Mobile | 12 |
| Products | Mobile Phones | iPad | 13 |
| Products | Mobile Phones | Samsung Galaxy | 14 |
| Products | Digital Cameras | test | 21 |
| Products | Printers and Toners | NULL | NULL |
| Products | test | abc | 20 |
| Products | test2 | NULL | NULL |
| Products | test3 | NULL | NULL |
| Products | Computer Accessaries | USB Cables | 23 |
| Products | Computer Accessaries | Network Cables | 24 |
+----------+----------------------+-------------------+-------------+
Can anybody tell me how can I get both level2 and level3?
有人能告诉我怎样才能同时达到二级和三级吗?
2 个解决方案
#1
1
If you have approach like if product has only second level category not 3 level category, you can try
如果你有方法,比如如果产品只有二级类别而不是三级类别,你可以试试
SELECT t1.name AS lev1,t2.name as lev2, t3.name as lev3,COALESCE( t3.category_id, t2.category_id ) FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.parent = t1.category_id
LEFT JOIN categories AS t3 ON t3.parent = t2.category_id WHERE t1.name = 'Products'
Which gives you category id of level 2 when there isn't any 3 level category
当没有任何3级类别时,它会给你2级的类别id
#2
1
Add to your fields this t2.category_id,
将这个t2.category_id添加到你的字段中,
SELECT t1.name AS lev1,
t2.name as lev2,
t2.category_id,
t3.name as lev3,
t3.category_id
FROM categories AS t1
LEFT JOIN categories AS t2
ON t2.parent = t1.category_id
LEFT JOIN categories AS t3
ON t3.parent = t2.category_id
WHERE t1.name = 'Products'
#1
1
If you have approach like if product has only second level category not 3 level category, you can try
如果你有方法,比如如果产品只有二级类别而不是三级类别,你可以试试
SELECT t1.name AS lev1,t2.name as lev2, t3.name as lev3,COALESCE( t3.category_id, t2.category_id ) FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.parent = t1.category_id
LEFT JOIN categories AS t3 ON t3.parent = t2.category_id WHERE t1.name = 'Products'
Which gives you category id of level 2 when there isn't any 3 level category
当没有任何3级类别时,它会给你2级的类别id
#2
1
Add to your fields this t2.category_id,
将这个t2.category_id添加到你的字段中,
SELECT t1.name AS lev1,
t2.name as lev2,
t2.category_id,
t3.name as lev3,
t3.category_id
FROM categories AS t1
LEFT JOIN categories AS t2
ON t2.parent = t1.category_id
LEFT JOIN categories AS t3
ON t3.parent = t2.category_id
WHERE t1.name = 'Products'