Skip to content
Advertisement

Join 3 tables and return whatever is not in the 2nd and 3rd

I have 3 tables (users, assignedtechnicians, assignedsupervisors)

enter image description here

technicianid and uspervisorid are foreign keys to user id stationid and regionid are foreign keys to some other tables

Essentialy users are assigned to posts that way what i want to do is output the users that are not assigned to a post, in this case:

enter image description here

I know that full outer join must be the way to go but i cant get it to work

Advertisement

Answer

You can write your query to be like this:

SELECT * from users U where U.user_id NOT IN (SELECT technicianid FROM assignedtechnicians) AND U.user_id NOT IN (SELECT uspervisorid FROM assignedsupervisors);
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement