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
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 !