Query based on 'does not exist' condition 1

[wapoc]

Hi,

Sorry if this is a daft question but I don't know how/if it can be done. I hope the following makes some sort of sense.

Let's say I have a table called 'forumtopics' that contains a field representing the topic's author. I then have another table called 'ignorelist' which has two fields; one represents another user and the other a reference to the aforementioned author. There can be many ignored authors for any one user.

For any given user, I'd like all the rows from the forumtopics table where there is no match for (user, author) held in the ignorelist table. Obviously it's easy to check for existence, but can the opposite be done in a single query?

I run MySQL 4.0.17.

Thanks,
Rob

 

[dalchri]

forumtopics
-----------
Subject
Body
AuthorID

ignorelist
-----------
UserID
AuthorID

SELECT
   Subject, 
   Body
FROM forumtopics
LEFT JOIN ignorelist
USING (AuthorID)
WHERE 
   UserID = 1 And
   ignorelist.AuthorID Is Null

This query returns the forum topics for one user.

 

[wapoc]

Excellent stuff, thanks a lot. I simply hadn't thought of/looked sufficiently into doing such a join where missing values would be represented but as null.

 

[r937]

dalchri, your left join approach is correct but there is a wee problem

suppose there is a row in forumtopics that has no ignorelist whatsoever

the left join will then attempt to produce a result row that has column values from forumtopics, but the columns from ignorelist will be null

however, you then stipulate WHERE UserID = 1

since null is not equal to anything, this row will be discarded!!

clearly, this is not correct, because it's a row from forumlist which has no matching ignorelist for user 1, and yet your query rejects it

any time you put a condition into the WHERE clause that involves a column from the right table, the left join is effectively turned into an inner join

the solution is to put the condition into the ON clause, to drive exactly which rows of the right table to be joined

this also means using ON syntax instead of USING

select Subject
     , Body
  from forumtopics as T
left outer
  join ignorelist as I
    on T.AuthorID = I.AuthorID
   and I.UserID = 1
 where I.AuthorID is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)