Quering Master and Transaction

[muhaidib]

I have 2 tables 1. Master and 2. Transaction
Table Master structure and data
Location , Itemno, TotalQty
113, 155001, 1000
125, 155001, 1654
127, 155001, 1895
133, 155001, 324
166, 265125, 1598

Table Transaction also has similar structure
Location , Itemno, QtyTransacted
119, 155001, 150
125, 155001, 165

I have to form a query to search master table for records in transaction and come out with location and itemno combination which is not present in master.

I tried with following query but I could not get desired result.
select location, Item from Table_Transaction where location not in (select location from Table_Master) and itemno in (select itemno from Table_Master)

Please guide and help.

 

[zhavic]

Try this

select Table_Transaction.location, Table_Transaction.Item
from Table_Transaction
left join Table_Master on Table_Master.location = Table_Transaction.location AND Table_Master.itemno = Table_Transaction.itemno
where Table_Master.location is null

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[muhaidib]

Thanks.

I have tried this query. I am not getting desired results.
Any other suggestion??

 

[muhaidib]

Zhavic,

Sorry for earlier reply. Your query works 100% correct. I had dropped the where condition which makes the difference.

Lot of thanks for getting me out of the problem.

With Regards.