Resolved Query two tables where columns are not equal

[rgEffects]

I have set up a content recordset and a links recordset. A link name is included in the links recordset. It's easy to write a query that compares the list of possible link names to the content and displays all the names that have been used on the list by doing this:

PHP Code:

SELECT links.linkName
FROM links, content
WHERE links.id = content.linkID
ORDER BY links.linkName ASC 


What I want is a list of the names NOT available. Everything I try either results in duplicates of the all names duplicated by the number of unused names or no results. Here's query that gives the list of used names duplicated by the number of unused names:

PHP Code:

SELECT links.linkName
FROM links, content
WHERE links.id <> content.linkID
ORDER BY links.linkName ASC 


I have tried all sorts of JOIN's and other logic. I must be missing something simple.

[Fou-Lu]

SELECT links.linkName
FROM links
LEFT JOIN content ON links.id = content.linkID
WHERE content.linkID IS NULL
ORDER BY links.linkName ASC

Try that. I'm terrible with null checking :P. I don't think MySQL support MINUS queries, but the above or using a subquery within the where should do the trick.

[rgEffects]

That did it. Never would have guessed that I'd have to put in WHERE content.linkID IS NULL and I didn't find anything like that in 40 minutes of searching...

Thanks again.

[Fou-Lu]

Yep. The concept is simple, if you drop the where clause and select the content.linkID as well, the left join guarantees all records from links, and only matching records from content. So only ones that have record in both would have content.linkID with a provided value. The rest would have null, and to remove the ones that are not null would give a list where content has no matching results to link.