How to write a query returning non-chosen records

Question

I have written a psychological testing application, in which the user is presented with a list of words, and s/he has to choose ten words which very much describe himself, then choose words which partially describe himself, and words which do not describe himself. The application itself works fine, but I was interested in exploring the meta-data possibilities: which words have been most frequently chosen in the first category, and which words have never been chosen in the first category. The first query was not a problem, but the second (which words have never been chosen) leaves me stumped.

The table structure is as follows:

table words: id, name
table choices: pid (person id), wid (word id), class (value between 1-6)

Presumably the answer involves a left join between words and choices, but there has to be a modifying statement - where choices.class = 1 - and this is causing me problems. Writing something like

select words.name
from words left join choices
on words.id = choices.wid
where choices.class = 1
and choices.pid = null

causes the database manager to go on a long trip to nowhere. I am using Delphi 7 and Firebird 1.5.

TIA, No'am

Answer 1

Something like that should do the trick:

SELECT name
FROM words
WHERE id NOT IN
   (SELECT DISTINCT wid   -- DISTINCT is actually redundant
    FROM choices 
    WHERE class == 1)

Answer 2

SELECT words.name
FROM
    words
    LEFT JOIN choices ON words.id = choices.wid AND choices.class = 1
WHERE choices.pid IS NULL

Make sure you have an index on choices (class, wid).

Answer 3

Maybe this is a bit faster:

SELECT w.name
FROM words w
WHERE NOT EXISTS
   (SELECT 1
    FROM choices c 
    WHERE c.class = 1 and c.wid = w.id)