I am trying to get the list of Items and couple of other details from Item_details table based on two primary conditions from other two tables I have joined. Please see the code below.
1. If Itemcoverage from Item_details is 1 (please note that there are rows for the items with Itemcoverage as "0" as well)
2. And there is no row in Item_History table with ItemUpdateID "9"
(In other words, I need to find out the Items that are having Item_details.Itemcoverage = '1' and doesn't have a row in the Item_History table with Item_History.ItemUpdateID not in (9))
select I.itemNumber,I.ItemName,I.ItemCode From MyDatabase..Item I
inner join MyDatabase..Item_details ID
on I.ItemID = ID.ItemID
inner join MyDatabase..Item_History IH
on I.ItemID = IH.ItemID
where I.ItemType = 6 and ID.Itemcoverage = '1' and IH.ItemUpdateID not in (9)
Thanks in advance