question

StanleyBarnett avatar image
StanleyBarnett asked

Finding Missing Child Rows

Hi, I need a query that lists the parents with missing child (records). I have two tables, parent and child. There are 1000 parent records and 980 child records. The query should only list the parents with NO child rows... Thanks, Stanley
sql serverselect2012
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
mail2anupam.kr avatar image
mail2anupam.kr answered
StanleyBarnett, a simple left outer join will do it. Sample code- Select p.* from parent p Left Outer Join child c on p.foreignkey_col=b.foreignkey_col WHERE b.foreignkey_col is NULL
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

StanleyBarnett avatar image StanleyBarnett commented ·
Hi, I fixed it by changing... WHERE bf.vfp_key = NULL to WHERE bf.vfp_key IS NULL Thanks, Stanley
1 Like 1 ·
StanleyBarnett avatar image StanleyBarnett commented ·
Hi mail2anupam.kr , I tried a modified version of your code and get 0 rows returned. I know there are about 200 rows without images. The da table is the parent and bf is the child. Select da.pk, da.system_id, da.vfp_key, bf.vfp_key FROM [Deedroom].[dbo].[DigitalAsset] da left outer join [Deedroom].[dbo].[BinFile] bf on da.vfp_key = bf.vfp_key WHERE bf.vfp_key = NULL Thanks, Stanley
0 Likes 0 ·
mail2anupam.kr avatar image mail2anupam.kr commented ·
Nice to hear that!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.