Help with SQL query

Tpaul_10
Tpaul_10 used Ask the Experts™
on
Experts,

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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial