We help IT Professionals succeed at work.

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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
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

Author

Commented:
That does not work because the Join won't return records where the is no Physical record.
CERTIFIED EXPERT
Top Expert 2012
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.

Author

Commented:
My error. Thanks works great.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.