MySQL从子表中包含和排除数据

Question

我的MySQL数据库中有一些表，我被一个查询卡住了，请帮忙。

表格包括：projectscategoriesproject_categoriesclient_categories

在projects table中，项目详细信息保存

在categories table中，所有类别都保存

在project_categories中，与该项目相关的所有类别都保存

在client_categories中，保存客户选择的项目类别(blacklisted或whitelisted(

现在我的场景是，我想找到所有项目都有客户选择的类别，所有类别都被列入白名单，，但没有任何黑名单类别

我将以的表格形式再次解释

项目表

+------------+--------------+
| project_id | project_name |
+------------+--------------+
| 1          | Proj_1       |
| 2          | Proj_2       |
| 3          | Proj_3       |
+------------+--------------+

类别表

+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
| 1           | Cat_1         |
| 2           | Cat_2         |
| 3           | Cat_3         |
+-------------+---------------+

项目类别表

+------------+-------------+
| project_id | category_id |
+------------+-------------+
| 1          | 1           |
| 1          | 2           |
| 1          | 3           |
| 2          | 1           |
| 2          | 3           |
| 3          | 3           |
+------------+-------------+

客户类别表

+-----------+-------------+--------+
| client_id | category_id | status |
+-----------+-------------+--------+
| 1         | 1           | white  |
| 1         | 2           | black  |
+-----------+-------------+--------+

这就是我的结构和伪数据。现在，对于这些数据，客户有类别_1白名单和类别_2黑名单，这意味着他不应该看到那些类别_2的项目，而应该只看到那些类别_1 的项目

对于该数据

Project_1->不应可见(包含类别_id 2，也包含类别_id 1但优先级较低(

Project_2->应可见(包含类别_id 1(

Project_3->应不可见(不包含类别_id 1(

我写了这个查询，如果我只想要白名单项目，这很好

SELECT projects.* FROM projects
INNER JOIN project_categories ON project_categories.project_id = projects.id
WHERE project_categories.category_id IN 
(SELECT category_id FROM client_categories WHERE status='white')

我对排除列入黑名单的类别的查询做了一些更改，但不起作用

SELECT projects.* FROM projects
INNER JOIN project_categories ON project_categories.project_id = projects.id
WHERE project_categories.category_id IN 
(SELECT category_id FROM client_categories WHERE status='white')
AND project_categories.category_id NOT IN 
(SELECT category_id FROM client_categories WHERE status='black')

Answer 1

这里基本上我们需要那些状态不是"黑色"的项目，所以我在这里获取状态为黑色的项目，然后将它们从完整的项目列表中排除

select * from projects p where project_id not in (
select p.project_id 
from client_categories cc 
join project_categories pc on cc.category_id = pc.category_id and cc.status  like 'b%'  
join projects p on p.project_id = pc.project_id where cc.status like 'b%'
); 

希望这能帮助

Answer 2

@SyedKhan我相信这个查询会给你想要的东西(如果我正确理解你想要的结果(。此查询只返回project_2，因为project_1有一个列入黑名单的类别，而不返回project_3，因为它没有列入白名单的类别：

SELECT `pr`.*
FROM `projects` AS `pr`
JOIN (
SELECT `p`.*,
GROUP_CONCAT(`cc`.`status`) AS `statuses`
FROM `projects` AS `p`
JOIN `project_categories` AS `pc` ON `pc`.`project_id` = 
`p`.`project_id`
JOIN `categories` AS `c` ON `c`.`category_id` = `pc`.`category_id`
JOIN `customer_categories` AS `cc` ON `cc`.`category_id` = 
`c`.`category_id`
GROUP BY `p`.`project_id`
) AS `der` ON `der`.`project_id` = `pr`.`project_id`
AND FIND_IN_SET('black', `der`.`statuses`) = 0
;

这是db fiddle中的例子。

希望这能有所帮助。