SQL query where there are no records

Not sure if this is possible, as it involves finding results where no records have been added.

Basically I have two related tables - lodges, and lodge_rates.

lodges:
LodgeID (PK)
Lodge
etc

lodge_rates
RatesID (PK)
LodgeID
Season
Rate
etc

And would like to return a list of all lodges where no rates have been added yet, i.e. no record in the lodge_rates table for a given LodgeID.

So if

SELECT * FROM lodges
INNER JOIN lodge_rates ON lodge_rates.LodgeID = lodges.LodgeID

Returns a list of lodges that have had their rates added, is there a query that would return lodges which have not had rates added?

I am using mySQL and phpMyAdmin.

Thank you!

change INNER JOIN to LEFT OUTER JOIN

And if you’re looking for JUST those without rates, you’d want to add a null check. If i

SELECT Lodge
  FROM  lodges
  LEFT OUTER JOIN lodge_rates ON lodge_rates.LodgeID = lodges.LodgeID
 WHERE Rate IS NULL

Thanks both - exactly what I was looking for.

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