user3006522 user3006522 - 13 days ago 7
MySQL Question

SQL JOIN issue to discard results

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

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.

Comments