Results 1 to 5 of 5

Thread: Excluding rows in a MySQL Query

  1. #1
    Junior Registered
    Join Date
    Jun 2004
    Posts
    3

    Question Excluding rows in a MySQL Query

    I'm trying to select rows from one table where there are NO matching records in another. I can't seem to formulate a query that does this AND doesn't result in duplicates...

    Table 1 : member

    contains members with unique IDs (member.id)

    Table 2 : photolink

    is a many-to-many relationship intermediate table. So it contains photolink.member and photolink.photo. photolink.member refers to the member and photolink.photo refers to a record in a third table.

    I need something that selects all the member records which do NOT have a corresponding record in photolink for a specific photo.

    So something like :

    SELECT member.* from member LEFT OUTER JOIN photolink ON photolink.member=member.id WHERE photolink.photo=217 GROUP BY member

    but then trying to find a way to exclude the members where the corresponding record in photolink was actually found, and avoiding getting duplicate member records in the output.

    Perhaps it's actually something like :

    SELECT member.* from photolink LEFT OUTER JOIN member ON photolink.member=member.id WHERE photolink.photo=217 - although this selects exactly the opposite of what I want - all the members which are related to this photo, as opposed to those that aren't.

    Any suggestions? Perhaps using an intermediate table, but I don't want to abuse the MySQL server too much.

  2. #2
    Junior Registered
    Join Date
    Jun 2004
    Posts
    3
    Ok - so I fixed my own problem the inefficient way : if you can think of a better solution, let me know. Here's what I did...

    1)

    CREATE TEMPORARY TABLE temp SELECT member.id,photolink.photo FROM member LEFT OUTER JOIN photolink ON member.id=photolink.member WHERE photo=217

    this creates an index of every link to photo number 217

    2)

    SELECT member.*,temp.photo FROM member LEFT OUTER JOIN temp ON temp.id=member.id WHERE ISNULL photo

    Since a LEFT OUTER JOIN will include all the members, where there is no corresponding entry in the photolink table (and now the temp table), there will be a NULL value in that field. So I can search for the NULLs and get my data that way.

    Either way, this sucks as it's heavy on the MySQL server, so my questions now are :

    1) Just how heavy is this? I don't usually use temporary tables so I don't know the impact.

    2) There must be a better solution - can't this be done with a single query and without the temporary table?

  3. #3
    Senior Member chromate's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    2,348
    Have you tried using a <> operator?

    SELECT member.* from photolink LEFT OUTER JOIN member ON photolink.member=member.id WHERE photolink.photo <> '217'

    Something like that?

  4. #4
    Junior Registered
    Join Date
    Jun 2004
    Posts
    3
    The issue with that is that since there can be multiple entries in the photolink table that refer to each member, you get multiple hits for each member, and you end up getting records the you don't want.

    Imagine that member 10 is linked to photos 216 and 217, there will be 2 records in "photolink" to create these two associations. Using your query, I won't get the one that refers to 217, but I'll still get the one that refers to 216. I want every member that isn't referred to by photo 217, so this isn't working. Also, in order to avoid duplicates, I'd have to GROUP BY member.id, but I'd still get records I don't want.

  5. #5
    Senior Member chromate's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    2,348
    Then I think you will have to use 2 select statements, but you don't need to create the temp table which will incur heavy overheads. You need to do a nested select.

Similar Threads

  1. MySQL driven AWS site
    By Best.Flash in forum User Owned Websites
    Replies: 9
    Last Post: 03-27-2004, 11:04 AM
  2. Excluding Rows in PHP?
    By Mike in forum Website Programming & Databases
    Replies: 18
    Last Post: 01-27-2004, 04:03 PM
  3. Query result column separation...
    By Stevens in forum Website Programming & Databases
    Replies: 41
    Last Post: 10-29-2003, 09:11 AM

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
  •