CODEWITHSUNDEEP
sqlsql-servert-sql
Drew
Drew

Reputation: 2663

Select All Values From Table That Match All Values of Subquery

So I have a list of values that is returned from a subquery and would like to select all values from another table that match the values of that subquery. Is there a particular way that's best to go about this?

So far I've tried:

select * from table where tableid = select * from table1 where tableid like '%this%'

Upvotes: 0

Views: 2342

Answers (4)

JoeFletch
JoeFletch

Reputation: 3960

I am reading a SQL Server book right now, and it highlights doing this using the EXISTS statement in the WHERE clause for speed and efficiency purposes. Here's a potential solution.

SELECT
     *
FROM
    tableName AS t
WHERE
    EXISTS(
        SELECT
            1
        FROM
            table1 AS s --as in subtable or something like that
        WHERE
            t.tableid = s.tableid
                AND
            s.tableid like '%this%'
            )

Upvotes: 0

syed mohsin
syed mohsin

Reputation: 2938

This will work 

  select * from table where tableid in
       (select tableid from table1 where tableid like '%this%')

= works when subquery returns 1 record only
in works when subquery returns 1 or more than one record only

Upvotes: 0

David Starkey
David Starkey

Reputation: 1840

select * from table where tableid IN 
(select tableid from table1 where tableid like '%this%')

A sub-query needs to return what you are asking for. Additionally, if there's more than 1 result, you need IN rather than =

Upvotes: 0

Sonam
Sonam

Reputation: 3466

select * from table where tableid in(select tableid 
from table1 
where tableid like '%this%')

Upvotes: 1

Related Questions