issue with MySQL query on multiple tables JOIN

Question

Here are some sql tables:

table USERS

id
1

---

table PROJECTS

id
1

---

table LOCATIONS

id
1
2

---

table USERS_x_PROJECTS

id      id_USER      id_PROJECT
1       1            1

---

table USERS_x_LOCATIONS

id      id_USER      id_LOCATION
1       1            1

---

table PROJECTS_x_LOCATIONS

id      id_PROJECT      id_LOCATION
1       1               1
2       1               2

---

Users are linked to Projects. The links between Users and Projects are stored into USERS_x_PROJECTS.
Users and Projects can be linked to Locations. The links are respectively stored into USERS_x_LOCATIONS and PROJECTS_x_LOCATIONS.

Now lets say i'm user #1. What i want to do is fetch all projects with their locations where none of the projects's location is matching one of mine.

Expected result: With the example above, the expected result is to get an empty array because both the user and the only project in the database are linked to the location #1.

Actual result: I get the project #1

What i tried:

SELECT
    p.id id,
    GROUP_CONCAT(DISTINCT l.id SEPARATOR "#") l_ids
FROM
    PROJECTS p
LEFT JOIN
    PROJECTS_x_LOCATIONS pl ON pl.id_PROJECT = p.id
LEFT JOIN
    LOCATIONS l ON l.id = pl.id_LOCATION
WHERE
    l.id != 1
GROUP BY
    p.id 

Thanks for the help!

Answer 1

I think something like that should work

SELECT P.id 
FROM PROJECTS P
INNER JOIN PROJECTS_x_LOCATIONS PL
    ON P.id = PL.id_PROJECT
WHERE PL.id_LOCATION NOT IN (
    SELECT id FROM USERS_x_LOCATIONS WHERE id_USER = 1
)

Answer 2

If the ids are sufficient:

select pxl.*
from PROJECTS_x_LOCATIONS pxl
where pxl.id_LOCATION not in (select uxl.id_location
                              from USERS_x_LOCATIONS uxl
                              where uxl.id_USER = ??
                             );

?? is your user id.

You can join in other tables if you want more than just the ids.