SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Is there a way to get results from negating a JOIN?

    Let me explain, I have a main table and an images table, I want to get the results from the main table that don't have an equivalent in the images table (hence entries without images).

    This works:

    Code MySQL:
    SELECT primaryKey, title FROM bookpedia WHERE primaryKey NOT IN 
    (SELECT DISTINCT a.`primaryKey`
    FROM book_images a
    GROUP BY a.`primaryKey`);

    But is extremely slow.

    A simple JOIN gets me the entries that have images so I thought that by negating it I could get the results a lot faster, but if I try to use the NOT keyword for a JOIN I get an error of course.

    Thanks.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i kinda like the way you think

    what you're looking for is the LEFT OUTER JOIN, which allows you, if i may coin a phrase, to go looking for a matching row and yet keep the result even if there wasn't one
    Code:
    SELECT bookpedia.primaryKey
         , bookpedia.title 
      FROM bookpedia 
    LEFT OUTER
      JOIN book_images 
        ON book_images.primaryKey = bookpedia.primaryKey
     WHERE book_images.primaryKey IS NULL
    the condition for the join is a matching column value

    for each matching row that is found, the join produces a row in the result set, which will shortly be filtered by the WHERE clause

    but when no such matching row is found, the row from the left table, bookpedia, is still included in the result set -- that's the difference between LEFT OUTER JOIN and INNER JOIN, where unmatched rows are dropped -- and in the result set, any columns from the right table, in this case the column used for the join, are set to NULL

    and then for the result set produced by the join, the WHERE condition now applies a rule -- keep only those rows of bookpedia which had no matching join column

    in other words, kind of like a negative join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I swear I tried this but was getting no results. Maybe I mixed the table orders or something.

    Well now it works, thanks a lot Rudy!
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com


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
  •