Help with mySQL query to find missing records

Databases
1

I have a query of the form:

SELECT DISTINCT 
lodges.LodgeID
, Lodge
FROM lodges
INNER JOIN lodge_activities_it
ON lodges.LodgeID = lodge_activities_it.LodgeID  
WHERE Product_Type = 'Property'

If it returns 995 records, but the same query without the join:

SELECT DISTINCT 
lodges.LodgeID
, Lodge
FROM lodges  
WHERE Product_Type = 'Property'

Returns 1000 records…

Is there a query that will easily show me the 5 records that are in the latter, but not in the former?

ie the ones that should have a related record in the lodge_activities_it table but don’t?

2

SELECT lodges.LodgeID , lodges.Lodge FROM lodges LEFT OUTER JOIN lodge_activities_it ON lodge_activities_it.LodgeID = lodges.LodgeID WHERE lodges.Product_Type = 'Property' AND lodge_activities_it.LodgeID IS NULL

3

Thank you - that’s perfect.

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.