SQL JOIN issue to discard results

Question

User #1 is linked to Sector #1

Project #1 is linked to Sector #1 AND Sector #2
Project #2 is linked to Sector #2 AND Sector #3

I want to retrieve projects - with their sectors - where the projects don't share any sectors with user #1

I've tried:

SELECT
    p.id,
    GROUP_CONCAT(DISTINCT s.id SEPARATOR "#") s_ids

FROM
    projects p

LEFT JOIN
    projects_x_sectors ps ON ps.id_project = p.id
LEFT JOIN
    sectors s ON s.id = ps.id_sector

WHERE
    s.id NOT IN (
        SELECT id_sector FROM users_x_sectors WHERE id_user = 1
    )

GROUP BY
    p.id

Exepected result:

fetched projects = [
    {
        id: 2,
        s_ids: 2#3
    }
]

Actual result:

fetched projects = [
    {
        id: 1,
        s_ids: 2
    },
    {
        id: 2,
        s_ids: 2#3
    }
]

MySQL tables:

users

id
1

projects

id
1
2

sectors

id
1
2
3

users_x_sectors

id    id_user    id_sector
1     1          1

projects_x_sectors

id    id_project    id_sector
1     1             1
2     1             2
3     2             2
4     2             3

Thanks

Answer 1

You don't need to join tables together -- unless you care about projects with no sectors at all.

To eliminate entire rows, you want to do the filtering after the aggregation. Here is one method:

SELECT ps.id_project,
       GROUP_CONCAT(DISTINCT s.id SEPARATOR "#") s_ids
FROM projects_x_sectors ps LEFT JOIN
     users_x_sectors us
     ON ps.id_sector = us.id_sector AND us.id_user = 1
GROUP BY ps.id_project
HAVING COUNT(us.id_sector) = 0;

The HAVING clause is checking that there are no matches.