CODEWITHSUNDEEP
sql-servert-sql
William T Wild
William T Wild

Reputation: 1032

Select using where with multiple element in a subquery

The following code does not work but I am using is as an example of what I am trying to accomplish:

SELECT thing_id
FROM table_of_things
WHERE table_of_things.thing_color, table_of_things.thing_flavor IN 
                          ( SELECT good_color,good_flavor FROM good_things )

EDIT: So apparently it wasent apparent from my code to SOME people. I am trying to use to get the things_id from the table_of_things using two criteria from the subquery.

Upvotes: 2

Views: 809

Answers (2)

Niladri Biswas
Niladri Biswas

Reputation: 4171

Using Join

SELECT t.thing_id
FROM table_of_things t
JOIN good_things gt 
ON t.thing_color = gt.good_color 
AND t.thing_flavor =  gt.good_flavor

Using Union

SELECT t.thing_id FROM table_of_things t WHERE t.thing_color IN ( SELECT good_color FROM good_things )
UNION 
SELECT t.thing_id FROM table_of_things t WHERE t.thing_flavor IN ( SELECT good_flavor FROM good_things )

Using EXIST

SELECT t.thing_id FROM table_of_things t 
WHERE EXISTS ( SELECT good_color,good_flavor FROM good_things)

Upvotes: 1

Abe Miessler
Abe Miessler

Reputation: 85036

You haven't asked a question but I suspect you need to change your query to the following:

SELECT thing_id
FROM table_of_things
WHERE table_of_things.thing_color IN  ( SELECT good_color FROM good_things )
AND table_of_things.thing_flavor  IN  ( SELECT good_flavor FROM good_things )

or maybe:

SELECT thing_id
FROM table_of_things as tot
INNER JOIN good_things as gt
ON tot.thing_color = gt.good_color AND tot.thing_flavor = gt.good_flavor

An in statement can only check one value, not two like you are trying to do.

Upvotes: 6

Related Questions