Need to find rows that are present in 1 table but not the other.

[nxm150]

I need to put a query together where the key is in 1 table but not in another table. How do I go about doing this? Thanks!!

 

[vtj]

Try an exception join or left outer join.

Select a.key1
from tbl_1 a exception join tbl_2 b
on a.key1 = b.key1

or

Select a.key1
from tbl_1 a left outer join tbl_2 b
on a.key1 = b.key1


VTJ

 

[PruSQLer]

vtj,

I think the proper term is EXCEPT in the first solution. In the 2nd, I think you need to add

Where b.key1 is null

 

[nxm150]

I am getting an -199 - ILLEGAL USE OF KEYWORD JOIN, TOKEN ( SELECT WAS EXPECTED

This is what I coded. I also added a WHERE clause

SELECT A.KEY
FROM TABLE_A A EXCEPT JOIN
TABLE_B B
ON A.KEY = B.KEY
WHERE A.COLUMN != 'VALUE'

 

[PruSQLer]

NXM,

You can't do an EXCEPT JOIN. Either do an Except or a Left Outer Join like:

Select a.key
from tableA a left outer join tableB b
on a.key = b.key
Where b.key is null

 

[nxm150]

Thanks! That query worked. I also want to add a 3rd table to the query which will limit the rows to a timestamp (in 3rd table) > certain date. Any addtional help would be appreciated.

 

[vtj]

The execption join may be database specific which is why I offered the second example. The term is exception join on the AS/400.

VTJ

 

[PruSQLer]

Select a.key
from tableA a left outer join tableB b
on a.key = b.key
, inner join tableC c
on a.key = c.key
and timestamp > <DATE VALUE>
Where b.key is null

 

[nxm150]

Sorry if I did not make my self clear. The 3rd table has a column that contains the timestamp when that record finshed processing. I want to check that timestamp against the date of our last release to limit the query. Wouldn't that mean, I would need another where clause (WHERE C.FINISHED_TIMESTAMP > RELEASE DATE)?

 

[PruSQLer]

Do you have a way to join Table A to Table C? Is the Release Date on Table A? If so, then yes, you can say WHERE C.FINISHED_TIMESTAMP > RELEASE DATE. Or am I missing something?

 

[nxm150]

All 3 tables have the same key. The completed date is on table C. The release date is not on any table, I will have to hard code that value.

 

[PruSQLer]

Okay, so the last example I coded above should then work when you replace DATE VALUE with the hardcoded date.

 

[nxm150]

I am using BMC to do my query. I tried your query and am getting

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL &quot;HLQ&quot;. SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ON FULL LEFT INNER RIGHT

 

[nxm150]

Never mind, I repeated table C twice. Will try query again.

 

[nxm150]

Query worked great. Thanks for you help!!