SQL Server select column from table where column in subquery

Question

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

hkutluay · Accepted Answer · 2013-01-17 09:56:28Z

2

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*"')

edited Jan 17, 2013 at 9:56

answered Jan 17, 2013 at 9:51

hkutluay

6,9742 gold badges37 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AntoonVs Over a year ago

i cant use or becase i need the nr to be in the first subquery and the second 1 and like that i get results from nr in one of the 2 subquery's

hkutluay Over a year ago

can you post a sample data and expected output?

AntoonVs Over a year ago

Tanx for the query i just need to add full text indexed on table2 will let you know result

AntoonVs Over a year ago

Can you maybe help me with enableing full text search on a table in sql server 2008 becase its greyd out and i can not get it enabled

Collectives™ on Stack Overflow

SQL Server select column from table where column in subquery

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related