Finding orphaned records

I am trying to find records that are orphaned in a data base after thier parent record (in another table) has been deleted.

Something happened to the constraints and I had no idea until now this had happened.

I have the faint rustle of a query in query sort of thing but my knowledge needs expansion.

PS: sorry for the cross post - just noticed that I was in the wrong forum before.

Thanks,
Matt

select * from childtable
where fk_column not in (select pk_column from parenttable)

thanks for the quick reply.

I tried the following:

select sArticleID from tblarticle
where sArticleID NOT IN (select sArticleID from tblprime)

but no result.

sArticleID id the PK in tblarticle
sArticleID id the FK in tblprime

What am I doing wrong here?

Thanks Matt

select sArticleID from tblprime
where sArticleID NOT IN (select sArticleID from tblarticle)

Thanks for the reply skhanal ,
Had to sleep on the problem and am just now back at the desk.

The last format works but is not the right way around for this problem.

I need to find the orphan data in tblarticle not in tblprime so the keys are reversed - no FK in tblarticle.

Tried truning round the code but that just didn't do it.

At least it is forcing me to research SQL again.

Thanks skhanal,

I recieved the solution in another forum (a mistaken cross post.

http://forums.databasejournal.com/sh...7406#post87406

using a right outer join and looking for a null value on the common column worked.

Thanks for the posts.

Matt

You might also try:

select Dependent.* from Dependent
where not exists (select * from Parent where Parent.KEYCOLUMN = Dependent.REFERINGCOLUMN)

Thanks Peter,

Works a treat.

And my next question for the panel:
What is the more correct form - outer join or the latest solution?

Thanks Matt

I think mine is preferred because it avoids a potential problem where the is null test will cause an error with columns declared as not null which is typically the case for Foreign Keys.
Just because the fk gets dropped doesnt mean the not null will go away.

My way expects that the relationship is truely a FK relationship in the dependent table column.

However, if some orphans are permissible then the other way should be preferred because it indicates that the fk relation isn't entirely true and the child row has a weaker relation to the parent row.

The technique that I posted is not bound to referencial integrity. You could use this way in any situation.

But most likely the not exist technique is recommanded.

Many thanks for the help and advise.

I can see differences in the approaches more clearly now. Thank you both for shining a little light on this for me.

The more I learn the more I discover there is still yet to learn.

Thanks
Matt

During Db setup sometimes I forget to add my key fields :(, howerver I can find duplicates in a single table using the following:
SELECT Field1, Field2, Field3, Field4, RecordID
FROM YourTable
WHERE (RecordID IN
(SELECT [RecordID]
FROM [YourTable] AS Tmp
GROUP BY [RecordID]
HAVING COUNT(*) > 1)) AND (RecordID <> 0)
ORDER BY RecordID

With some modification you can utilize this to search for orphand records.