Help with SQL query


I am trying to find out the list of itemnumbers from item table and don't have a corresponding row in itemdetails  table with a condition and following is the SQL I am using and not giving me correct results, could you please correct it?

select  ItemNummber From DB1..Item where ItemNumber not in (select itemnumber from DB1..itemdetails where itemDiscount = 1)
and  itemtypeid in (1,10)

Thanks in advance
Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
SELECT i.ItemNumber
FROM Item i
   LEFT JOIN ItemDetails id ON i.ItemID = id.ItemID   -- LEFT means return all from item
WHERE id.ItemID IS NULL   -- that are not found in ItemDetails table
   AND id.itemtypeid in (1,10)  -- the other condition

Open in new window

This image may also help...SQL Joins
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.