Query Join Question 1

[cutiger93]

Wish I could draw a diagram on this one. Here goes.

If I have table A and Table B and I want only the records from table A that do Not Match table B then how would I write this in a query.

Basically I want to know all the records in table A that are not in Table B.

Any help would be appreciated.

 

[sunila7]

Hi,

Try this.. Iam assuming that u have some kingd of ID field in both tables



select * from TableA A
Where Not Exists (SELECT * from TAbleB B Where B.ID = A.Id )

Select * from TableA A
Where A.ID Not IN (SELECT ID from TableB)

Sunil

 

[cutiger93]

Sorry I should have included the following:

Table A and B have two columns that I am keying on. Warehouse and Item. I want to know all items and warehouses in A that are not in B.

select * from A where A.warehouse and not in A.item.

Thanks.

 

[swampBoogie]

select * from TableA a
Where Not Exists (SELECT * from TAbleB b 
Where b.Item = a.Item
and b.Warehouse = a.Warehouse )

 

[ClaireHCM]

select A.* from tableA A left join tableB B on A.item = B.item and A.warehouse = B.warehouse
where B.item is null and b.warehouse is null

 

[cutiger93]

thanks SwampBoogie. This worked great.
I had to resort to putting my Queries into temp tables and then use your suggestion. It would have worked without the temp tables, but the query was very long in nature.

select * from (select whs,item from tab1 inner join tab2 where ...) Q1
where not exists(select * from Q2 WHERE Q1.ITEMID=Q2.ITEMID AND Q1.WHSE=Q2.WHS)

 

[SQLSister]

I believe Claire's methodolgy would be more efficient.

 

[cutiger93]

Try Claire's I could not get it to work. I still received the null results because of the left join. I believe I was doing the query correct.