SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    334
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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:-
    Code:
    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:-
    Code:
    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
    Last edited by ramasaig; May 19, 2010 at 06:33. Reason: Problem solved
    Tim Dawson
    Isle of Mull, Scotland

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    You were almost there
    Code MySQL:
    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

  3. #3
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    334
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello Guido, Many thanks. All that matters is that we get there in the end !

    Tim
    Tim Dawson
    Isle of Mull, Scotland


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •