Need SQL help on query

[etishler]

I have been programming for a long time, but I am a relative newbie with SQL.

I have two tables with transactions. One table contains ALL transactions, the second table is a subset of the first with only a specific set of transactions. The columns differ between the two tables, but there is enough relational information for me to match all of the subset transactions with the master transaction table.

In some odd cases the subset transaction is inserted, but the corresponding entry is not added to the master transaction table.

I need to write a query that shows which transactions in the subset table do not appear in the master table.

Can someone help me with this?

Thanks,

Eric

 

[PHV]

Something like this ?
SELECT * FROM YourSubSetTable S
WHERE S.ForeignKeyOfMasterTable NOT IN
(SELECT PrimaryKey FROM YourMasterTable)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[rrrkrishnan]

Something like this should work.
select * from sub_trans a
where not exists (select * from all_trans b
where b.join_col1=a.join_col1
and b.join_col2=a.join_col2 ......)

 

[etishler]

PHV: It tried this (per your suggestion) and it do not like the keyword IN (as in: NOT IN)

rrrkrishnan: I tried your suggestion as well and it worked!

Thanks to all ...

E

Eric Tishler
Software Architect
Resolute Partners, LLC

 

[PHV]

Which db don't like the IN keyword ?