Query to show all ID's except the ones that have a specific instrument-CodePudding

So I have a table which looks like this:

What would be the best query to exclude every "stuknr" that has atleast 1 "saxofoon" in the "instrumentnaam" column?

CodePudding user response：

To select all stuknr where there is not stuknr with a saxofoon:

SELECT *
FROM table
WHERE stuknr not in (
   SELECT stuknr 
   FROM TABLE 
   WHERE INSTRUMENTNAAM = ‘saxofoon’)
;

CodePudding user response：

SELECT STUNKR 
FROM TABLE 
WHERE INSTRUMENTNAAM != ‘saxofoon’;

Here != (or <>, which are equivalent, see this) means "not equal".

CodePudding user response：

You can do this with a NOT IN sub-select

select distinct 
      YT.stunkr
   from
      YourTable YT
         where YT.stunkr NOT IN ( select distinct stunkr
                                     from YourTable
                                     where InstrumentNaam = 'saxofoon' )

So the sub-select is getting all IDs that DO have saxofoon and the primary select/from is getting where the ID is NOT in the secondary.