Mysql Query Exclude a certain entry

Question

Hi all,

I'm trying to SELECT entries of a certain type, name, EXCLUDING entries with the same name in a particular id. The last part is the tricky part that I can't get my head around.

SELECT name FROM table WHERE type = $type AND (name is not contained in an entry with id).

I want the result to be the set of all names that ARE of a certain type but NOT already included in a current id.

Do I need to execute two queries here? Or can I condense it to one.

Thanks.

Answer 1

You can use a subquery for this:

SELECT name
FROM table
WHERE type = $type
AND name NOT IN (SELECT entry FROM t WHERE id = $id );

Answer 2

Do you mean like so:

SELECT name FROM table WHERE type = $type 
                       AND name not in ('larry','moe','curly').

Could you provide a little more detail on your schema? Concrete examples always help.

Answer 3

If you compare multiple values to a row returned by a subquery this way:

SELECT name FROM table
WHERE type = $type 
AND (name, criteria, rank) NOT IN 
  (SELECT name, criteria, rank FROM t WHERE id = $id );

You must make sure the list (name, criteria, rank) matches the columns selected in the subquery. You shouldn't use SELECT *.