Select Query for a Parent-Child Relationship setup

[SBK]

Thanks in advance for any help, comment or otherwise, anyone can provide for
my question. This is the first time in over three years I have worked with a
database and I have inherited two tables that are related but have not
relationship built in. My primary goal right now is to run a select query
that will return a value of a Prod. ID from a Child table after referencing
the Parent Table of the same type (Prod ID) where there is not a related
Parent Field. Parent Table Prod ID references many Child Table Prod ID. In
essence, work backwards and find what parent information is missing from the
extra fields in the child table. Since the Parent information is missing
there is not a built relationship – which is my ultimate goal. Returning this
select query info would allow me to amend the Parent table by adding the
missing fields from the child table. Again, Thanks in advance for any
assistance.

 

[Tom Ellison]

Dear SB:

How about:

SELECT C.*
FROM Parent P
RIGHT JOIN Child C
ON C.ID = P.ID
WHERE P.ID IS NULL

This should give you the missing Parent table rows by giving you the Child
rows that reference them. You could shorten the results:

SELECT DISTINCT C.ID
FROM Parent P
RIGHT JOIN Child C
ON C.ID = P.ID
WHERE P.ID IS NULL

You must substitute the actual names of your tables.

Tom Ellison