luca luca - 6 months ago 10
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

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                    
Comments