MySQL complex join query

Question

I have 2 tables
1)master_category

 cat_id             cat_name
------------------------------
   1                   Mobiles
   2                   Motorola
   3                   Microsoft
   4                   Mobile Accessories
   5                   Samsung
   6                   Sony
   7                   Samsung Accessories
   8                   Galaxy S6
   9                   Laptops
   10                  HP
   11                  Sony Vaio
   12                  Dell
   13                  Dell Accessories
  --------------------------------------------

2)category_group ( In this table grouping category master datas - main_group and Sub_group are cat_ids from master_category table

   id        Main_group        Sub_group
 -----------------------------------------------------
   1             1                 2
   2             1                 3
   3             1                 4
   4             1                 5
   5             1                 6
   6             4                 7
   7             5                 8
   8             9                 10
   9             9                 11
   10            9                 12
   11            12                13
 ---------------------------------------------

in category_group category Mobile have sub categories like motoroal,microsoft,samsung,mobile accessories and mobile acessories have sub category samsung accessories, Also samsung have sub groups galaxy s6

Laptops are also grouped like that

when i search mobiles i need to write a query to get a result as follows

 cat_id              cat_name
-------------------------------
   2                    Motorola 
   3                    Microsoft
   4                    Mobile Accessories
   5                    Samsung
   6                    Sony
   7                    Samsung Accessories
   8                    galaxy s6
 ---------------------------------------------

when i search Laptops i need to get a result as follows

   cat_id                cat_name
 ----------------------------------
     10                     HP
     11                     Sony Vaio
     12                     Dell
     13                     Dell Accessories
---------------------------------------------

Nothing terribly complex about this. It's a bit unusual to have manufacturers and product categories in the same table though. Was just composing the same answer as KA_lin, but he beat me to it. — PaulProgrammer, Sep 01 '15 at 14:21
possible duplicate of [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree) — Alex, Sep 01 '15 at 20:29

score 1 · Answer 1 · answered Sep 01 '15 at 14:20

It is not that complex, you have to join with an alias otherwise you will get something like: 'Column 'id' in field list is ambiguous' (hope I don`t have a typo):

select products.id,products.cat_name from category_group 
join master_category as category on master_category.cat_id=category.Main_group
join master_category as products on master_category.cat_id=products.Sub_group
where category.cat_name='Mobiles'

Also take a look at this: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

it doesn't provide result `7 Samsung Accessories` `8 galaxy s6` — Shameem, Sep 01 '15 at 15:48

score 1 · Accepted Answer · edited May 23 '17 at 11:58

As I already marked I believe that is duplicate of https://stackoverflow.com/a/192462/4421474

But since this particular question has particular dataset, here is my approach (not universal but working)

http://sqlfiddle.com/#!9/2787a/1

SELECT names.*
FROM master_category mc
LEFT JOIN category_group sc #level1
ON sc.main_group = mc.cat_id
LEFT JOIN category_group sc2 #level2
ON sc2.main_group = sc.sub_group
LEFT JOIN master_category names
ON sc.sub_group = names.cat_id
  OR sc2.sub_group = names.cat_id
where mc.cat_name = 'Mobiles'
order by names.cat_id;

MySQL complex join query

2 Answers2