SQL 查询从 One Parent 的所有类别中选择产品
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9894320/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query to select products from all categories of One Parent
提问by Vehlad
I have a Product Table with this structure
我有一个具有这种结构的产品表
id, title, slug, details, category(FK category.ID)
And a Category Table: id, name, slug, parent
和一个类别表：id、name、slug、parent
Categories have only 1 level If there is no parent then parent field is 0 else id of parent category saved
类别只有 1 个级别 如果没有父级，则父级字段为 0，否则已保存父级类别的 id
Suppose I have categories structure like this:
假设我有这样的类别结构：
FRUITS
|---- APPLE
|---- MANGO
|---- BANANA
I use a Category slug to query products from a category
我使用 Category slug 从类别中查询产品
category.php?cat=apple category.php?cat=mango
category.php?cat=apple category.php?cat=mango
1st query:
第一个查询：
select id,name from category WHERE slug='$catslug'
After getting ID of category then use query to get results of that ID from products table
获取类别 ID 后，然后使用查询从产品表中获取该 ID 的结果
select * from products where category=$categoryid
What query is required if I want to fetch all products posted in FRUITS?
如果我想获取 FRUITS 中发布的所有产品，需要什么查询？
category.php?cat=fruits
I want to get all products posted in apple, mango, banana (all children of fruits)
我想将所有产品发布在苹果、芒果、香蕉（所有水果的孩子）中
Only the child IDs are saved in product table how do I link parent category apple with these?
只有子 ID 保存在产品表中如何将父类别苹果与这些链接？
回答by mellamokb
You can look for categories linked to products where the parent category is the fruit category by using multiple joins. Here ccrefers to the child category and cprefers to the parent category. We retrieve all products having a child category equal to the products category, and having a parent category to that child category equal to FRUITS:
您可以使用多个联接查找链接到父类别为水果类别的产品的类别。这里cc指的是子类，cp指的是父类。我们检索所有产品的子类别等于产品类别，并且该子类别的父类别等于FRUITS：
select *
from products p
inner join categories cc on cc.category = p.category
inner join categories cp on cp.category = cc.parentcategory
where cp.category = 'FRUITS'
If this needs to be a generic solution that handles both parent and child categories seemlessly, you can check for a match at either level (assuming all category names are unique, and every product has a category):
如果这需要成为无缝处理父类别和子类别的通用解决方案，您可以检查任一级别的匹配项（假设所有类别名称都是唯一的，并且每个产品都有一个类别）：
select *
from products p
inner join categories cc on cc.category = p.category
left join categories cp on cp.category = cc.parentcategory
where cc.category = '$category' or cp.category = '$category'
回答by tarmaq
1) You don't need to use two queries, you can use join:
1）你不需要使用两个查询，你可以使用join：
SELECT p.id, p.title, p.slug ...
FROM products p
JOIN categories c ON c.id = p.category
WHERE c.slug = 'apple'
2) You can select all fruit products with this query:
2) 您可以使用此查询选择所有水果产品：
SELECT id, title, slug ...
FROM products
WHERE category IN (
SELECT id
FROM categories
WHERE slug = 'fruits'
UNION
SELECT c.id
FROM categories c
JOIN categories c2 ON c2.id = c.parent
WHERE c2.slug = 'fruits'
)
回答by Vehlad
I used 2nd query suggested by tarmaq
我使用了 tarmaq 建议的第二个查询
SELECT *
FROM products
WHERE category IN (
SELECT id
FROM category
WHERE slug = '$cats'
UNION
SELECT c.id
FROM category c
JOIN category c2 ON c2.id = c.parent
WHERE c2.slug = '$cats')";
Its showing results from products table only Not getting Category Name, Category Details from category table
它仅显示产品表中的结果 未从类别表中获取类别名称、类别详细信息
I tried this:
我试过这个：
SELECT *
FROM products
WHERE category IN (
SELECT *
FROM category
WHERE slug = '$cats'
UNION
SELECT *
FROM category c
JOIN category c2 ON c2.id = c.parent
WHERE c2.slug = '$cats')";
result: Error in Query.
结果：查询错误。
also I want to JOIN users table in above query and to show author name(users.name) alongwith product (author id saved in product table which is FK from users table users.id is unique)
我还想在上面的查询中加入用户表并显示作者姓名（users.name）和产品（作者 ID 保存在产品表中，这是来自用户表 users.id 的 FK 是唯一的）
JOIN users on products.author = users.id
Where to put this Join statement in above query
在上面的查询中将此 Join 语句放在哪里
EDIT- I joined user table with author name with this query
编辑- 我用这个查询加入了带有作者姓名的用户表
SELECT *
FROM products
JOIN users U ON U.id = products.author
WHERE category IN (
SELECT id
FROM category
WHERE slug = '$cats'
UNION
SELECT C.id
FROM category C
JOIN category C2 ON C2.id = C.parent
WHERE C2.slug = '$cats')
ORDER BY products.date DESC
now want to fetch data from category table catagory.name, category.details with this query
现在想使用此查询从类别表 catagory.name, category.details 中获取数据
