Finding records in one table NOT included in another

In my database I have two tables properly linked via indices (primary index in one is secondary index in the other). I can successfully update one table from the other where the indices match.

However, not all rows in the first table have corresponding rows in the second. I want to create a query (to run in phpMyAdmin) that will find the rows in the table ‘listings’ that are NOT in the table ‘latlong’. I’ve tried a variety of joins, along the lines of:-

SELECT listings.bus_id, listings.bus_name FROM `listings` LEFT JOIN latlong ON listings.bus_id NOT IN latlong.bus_id

but this is clearly wrong (it actually runs, but produces 948 pages of results, instead of the expected one or two).

I only need this work in phpMyAdmin, so don’t want to resort to PHP to create a list of the ‘bus_id’ values in ‘latlong’. I could combine the two tables, but would prefer to keep them separate as it’s easier to update the smaller ‘latlong’ table manually.

I have tried the MySQL manual, but haven’t hit on anything helpful there. Surely searching for ‘missing’ rows shouldn’t be all that difficult ? Any suggestions, please ?

Later: OK, I got it eventually. The code should be:-

SELECT listings.bus_id, listings.bus_name FROM listings LEFT JOIN latlong ON listings.bus_id = latlong.bus_id WHERE latlong.bus_id IS NULL

You were almost there


SELECT 
    listings.bus_id
  , listings.bus_name 
FROM `listings` 
LEFT JOIN latlong 
ON listings.bus_id = latlong.bus_id
WHERE latlong.bus_id IS NULL

Hello Guido, Many thanks. All that matters is that we get there in the end !

Tim