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

Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Jun 23 '20 at 09:17

score 0 · Accepted Answer · answered Jun 17 '20 at 12:56

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"
)

score 0 · Answer 2 · answered Jun 23 '20 at 09:58

At the end I tried the follwoing and it worked:

select product.pid, product.label
from ((product  inner join line_item on product.pid = line_item.pid) inner join orders on orders.oid = line_item.oid)
where not orders.date ='2003-05-13'
group by product.pid;

Thanks a lot for your answers, they helped me to find the right path to solve it

Listing data with join in SQL

2 Answers2