MySQL include and exclude data from child table

Question

I have some tables in my MySQL database, I am stuck on one query, please help.

Tables are: projects categories project_categories client_categories

In projects table, project details are saved

In categories table, all categories are saved

In project_categories, all categories related to this project are saved

In client_categories, client's selected project categories are saved (either blacklisted or whitelisted)

Now my scenario is, I want to find all projects having client's selected categories with all whitelisted categories, but without any blacklisted category.

I will explain it again in tabular form

Projects Table

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

Categories Table

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

Project Categories Table

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

Customer Categories Table

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

So this is my structure and dummy data. Now for this data, client has category_1 whitelisted and category_2 blacklisted, which means he should not see those project which has category_2 and should only see those project which has category_1

For this data

Project_1 -> should not visible (contains category_id 2, also contain category_id 1 but this has less priority)

Project_2 -> should be visible (contains category_id 1)

Project_3 -> should not visible (does not contain category_id 1)

I have this query written, this works fine if I only want whitelisted projects

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')

I changed query little bit for excluding blacklisted categories, but doesn't work

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

here basically we required those projects which are not status 'black' so here I have fetch projects with status black and then exclude them from complete projects list

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%'
); 

hope this will help

Answer 2

@SyedKhan I believe this query will give you what you are looking for (if I understand your desired result correctly). This query returns only project_2 because project_1 has a category that is blacklisted and not project_3 because it doesn't have a whitelisted category:

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
;

Here is the example in db fiddle.

Hope this helps.