Excluding rows in a MySQL Query

[nlvp]

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.

[nlvp]

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?

[chromate]

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?

[nlvp]

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.

[chromate]

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.