CODEWITHSUNDEEP
mysqlset
Dirk
Dirk

Reputation: 6884

Mysql Query Exclude a certain entry

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.

Upvotes: 2

Views: 9894

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562270

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 *.

Upvotes: 2

dustmachine
dustmachine

Reputation: 10814

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.

Upvotes: 1

Zed
Zed

Reputation: 57648

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 );

Upvotes: 9

Related Questions