Listing data with join in SQL

Question

I have the following question related to joining tables together in SQL:

List all products that have not been sold on 13.05.2003. Taking into account the tables Orders, LineItem and Product.

What I have coded is the following:

SELECT p.Pid, p.Label
 from Product p natural join line_item l
natural join Orders
where Date <> "2003-05-13"

The problem is that when I execute this code it appears more data tan it should be and I am not sure how to get rid of duplicates with join.

Thank you in advance

Answer 1

It's safer to use NOT EXISTS. For example:

select *
from product p
where not exists (
  select null 
  from lineitem i
  join orders o on o.oid = l.oid and l.pid = p.pid
  where date <> "2003-05-13"
)

Or you can use NOT IN:

select *
from product 
where pid not in (
  select l.pid
  from lineitem i
  join orders o on o.oid = l.oid
  where date <> "2003-05-13"
)