CODEWITHSUNDEEP
mysqlsqldatabaseselect
user3733831
user3733831

Reputation: 2926

How can I make this SELECT in hierarchical data modal using mysql?

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.

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. 

+----------+----------------------+-------------------+-------------+
| 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?

Upvotes: 1

Views: 37

Answers (2)

Adrian Cid Almaguer
Adrian Cid Almaguer

Reputation: 7791

Add to your fields this 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'

Upvotes: 1

S A M
S A M

Reputation: 151

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

Upvotes: 1

Related Questions