luca luca - 1 year ago 46
SQL Question

Query to retrieve all user except those returned from another query

I have to write a query through Spring data to mySql database.
I have a table User and a table Fleet with a many to many relationship, so I have another table with id_username and id_fleet.
Now I would like to know all user that I may add to a fleet, so I need all user except those that already have the specified fleet.
I tried with a sample query in Mysql workbench like this:

SELECT username FROM dart.user where enabled=true NOT IN (Select id_username from dart.fleet_Has_User where dart.fleet_Has_User.id_fleet= '8' )


but it doesn't remove user of second query. Could you help me ? Thanks

Answer Source

Try to use NOT IN like

SELECT username FROM dart.user 
where enabled=true 
and id_username NOT IN (Select id_username from dart.fleet_Has_User where dart.fleet_Has_User.id_fleet= '8' )
    ^^^^^^^^^^^
    yourIdusername col of dart.user table