How To Use Not Exists In A Sql Query With W3schools?

I have an issue with not exists sql query at w3schools I want to select all customers that work with shipperid = 1 BUT not shipperid = 3. I tried the following: select o1.customeri

Solution 1:

I'm fairly certain that the problem lies in the way you're joining the correlated subquery, on orderid = orderid. I'm not familiar with this dataset, but it seems surprising that the same order would have different shippers, and it adds a condition not found in your 'correct' answer. This should work:

select o1.customerid
      ,o1.shipperid
from orders as o1
where o1.shipperid = 1 
and not exists (
    select o2.orderid 
    from orders as o2
    where o1.customerid = o2.customerid
    and o2.shipperid = 3)
order by customerid
;