CODEWITHSUNDEEP
sql-server-2008subquery
AntoonVs
AntoonVs

Reputation: 91

SQL Server select column from table where column in subquery

I have wrong result in my query because I don't get any results but the definitely are there.

QUERY 

 select nr 
 from table1 
 inner join table2 on table2.nr = table1.nr
 where table1.nr in (select nr 
                     from table2 
                     where columnn like '%value%') 
   and nr in (select nr from table2 where columnn like '%other value%')

When I only use first subquery I get results, but with the second subquery in it I don't

Upvotes: 0

Views: 5130

Answers (1)

hkutluay
hkutluay

Reputation: 6944

Use OR instead of AND

select nr from table1 
 inner join table2 on table2.nr = table1.nr
 where table1.nr in (select nr from table2 where columnn like '%value%') or nr in 
(select nr from table2 where columnn like '%other value%')

And join is useless if it is exact same query that u use.

Elegant way is

select nr from table1 
 inner join table2 on table2.nr = table1.nr
 where CONTAINS(table2.column, '"*value*" OR "*other value*"')

Upvotes: 2

Related Questions