我存储多层次类别。桌子看起来像这样：
类别表
+-------------+---------------+--------------------+
| category_id | category_name | parent_category_id |
+-------------+---------------+--------------------+
| 1 | Clothing | NULL |
| 2 | Footwear | NULL |
| 3 | Men | 1 |
| 4 | Women | 1 |
| 5 | Shirts | 3 |
| 6 | T-Shirts | 3 |
| 7 | Jeans | 3 |
+-------------+---------------+--------------------+
公司类别映射表
+------------+------------+-------------+
| cmp_cat_id | company_id | category_id |
+------------+------------+-------------+
| 1 | 7 | 5 |
| 2 | 7 | 6 |
| 3 | 7 | 7 |
+------------+------------+-------------+
我想从分类表中获取有关公司id的所有分类、子分类、子分类。我很困惑如何做到这一点。我可以为商店公司类别创建一个新表吗？
我想在下拉列表中显示输出。
我有三个下拉列表，一个是主类别下拉列表，第二个是子类别下拉列表，第三个是子类别下拉列表。下拉菜单是按公司填写，即不同的公司有不同的类别。
输出显示屏。
对不起我的英语。
谢谢你帮我。
最佳答案
试试这个代码。
SELECT c1.`category_name` as `category` , c2.`category_name` as `sub-category`, c3.`category_name` as `sub-sub-category` FROM `categories` as c1
LEFT JOIN `categories` as c2 ON c1.`category_id`=c2.`parent_category_id`
LEFT JOIN `categories` as c3 ON c2.`category_id`=c3.`parent_category_id`
where c1.`parent_category_id` IS NULL
关于公司id
SELECT cm.`company_id`, c1.`category_name` as `category` , c2.`category_name` as `sub-category`, c3.`category_name` as `sub-sub-category` FROM `categories` as c1
LEFT JOIN `categories` as c2 ON c1.`category_id`=c2.`parent_category_id`
LEFT JOIN `categories` as c3 ON c2.`category_id`=c3.`parent_category_id`
INNER JOIN `company_category_mapping` as cm ON cm.`category_id`=c3.`category_id`
where c1.`parent_category_id` IS NULL
如果需要获取特定公司id的数据。
例如：公司id=7
SELECT cm.`company_id`, c1.`category_name` as `category` , c2.`category_name` as `sub-category`, c3.`category_name` as `sub-sub-category` FROM `categories` as c1
LEFT JOIN `categories` as c2 ON c1.`category_id`=c2.`parent_category_id`
LEFT JOIN `categories` as c3 ON c2.`category_id`=c3.`parent_category_id`
INNER JOIN `company_category_mapping` as cm ON cm.`category_id`=c3.`category_id` AND cm.`company_id`=7
where c1.`parent_category_id` IS NULL