NO_DATA_FOUND exception

[HajdukSplit]

I have 2 tables, TABLE_A and TABLE_B.
TABLE_A has a field B_ID which contains the ID of corresponding record in the TABLE_B. In the TABLE_B some of the referenced records were deleted.
What I need to do is to run through TABLE_A and select all B_ID for which there is no corresponding record in the TABLE_B.

I've tried this using the NO_DATA_FOUND exception, but this only gives me the first B_ID for which there is no corresponding record in the TABLE_B.

I'm sure this is rather basic, but since I'm a total newbie on this topic I'm asking for your help.

 

[jcaulder]

I think this should do it:

select table_a.B_ID from table_a left outer join table_b 
on table_a.B_ID = table_b.ID
where table_b.ID IS NULL

 

[bborissov]

SELECT Table_A.*
FROM Table_A
LEFT JOIN Table_B ON Table_A.B_Id = Table_B.Id
WHERE Table_B.Id IS NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

select B_ID from Table_A
EXCEPT
SELECT ID from Table_B

Check this blog post

http://blogs.lessthandot.com/index....gn/the-ten-most-asked-sql-server-questions--1

Your problem is #4.

PluralSight Learning Library