SQL Join to Return Rows With No Rows in Child Table

I have a table of People and a related table of their physicals. I need to do a query to find everyone who does not have a record in the Physical Table. The following SQL shows me everyone who does have a physical, so basically I need everyone who isn't in this query.

SELECT     Person.FullName, Physical.PhysicalDate
FROM         Person INNER JOIN
                      Physical ON Person.PersonKey = Physical.PhysicalParentKey
rpotashAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
That does not work because the Join won't return records where the is no Physical record.
Have you tried it?

If case you were not aware this is exactly the reason I am using a LEFT OUTER JOIN.  This query will return all the data in Persons that is not in Physical.

Try it, you may be impressed.
0
 
Anthony PerkinsCommented:
This is one option:

SELECT  Person.FullName,
        Physical.PhysicalDate
FROM    Person
        LEFT JOIN Physical ON Person.PersonKey = Physical.PhysicalParentKey 
WHERE	Physical.PhysicalParentKey IS NULL

Open in new window

0
 
rpotashAuthor Commented:
That does not work because the Join won't return records where the is no Physical record.
0
 
rpotashAuthor Commented:
My error. Thanks works great.
0
All Courses

From novice to tech pro — start learning today.