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

i have used hierarchical data modal store products in database. products have main , subcategory, , result when retrieving full tree of products.

select t1.name lev1,              t2.name lev2,               t3.name lev3, categories t1     left join categories t2          on t2.parent = t1.category_id     left join categories t3          on t3.parent = t2.category_id 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 , toners  | null              | | products | test                 | abc               | | products | test2                | null              | | products | test3                | null              | | products | computer accessaries | usb cables        | | products | computer accessaries | network cables    | +----------+----------------------+-------------------+ 

my question need select level2 , level3 category id along select query.

i tried this:

select t1.name lev1,                  t2.name lev2,                   t3.name lev3,                  t3.category_id categories t1     left join categories t2          on t2.parent = t1.category_id     left join categories t3          on t3.parent = t2.category_id t1.name = 'products' 

but provide level3 ids. 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 , 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 tell me how can both level2 , level3?

if have approach if product has second level category not 3 level category, can try

select t1.name lev1,t2.name lev2, t3.name lev3,coalesce( t3.category_id, t2.category_id ) categories t1 left join categories t2 on t2.parent = t1.category_id left join categories t3 on t3.parent = t2.category_id t1.name = 'products' 

which gives category id of level 2 when there isn't 3 level category