How to use in operator

_AK_

yes you can give... You can use it something like this: declare @tt varchar(100) declare @sql varchar(100) set @tt = '''0004'','+'''0005'','+'''0006''' set @sql='select * from mst_employees where emp_no in ('+@tt+')' exec (@sql)

Best Regards, Apurva Kaushal

Uma Kameswari

If I want to use with in a subquery then how to use? Regards, Uma

_AK_

same way you just need to create the dynamic query..

Best Regards, Apurva Kaushal

Uma Kameswari

It means we cannot use a single query.We have to give it in the loops i.e with cursors.Is that right Thank u. Regards, Uma

_AK_

No not at all... What exactly is your requirement?

Best Regards, Apurva Kaushal

Uma Kameswari

I have two tables. one contians a column called keywords which contains individual words. other contains a column called list which contains a list of words separated by comma.Now i want to write a query to retrieve details from first table by searching the second table.How to do this Regards, Uma

Colin Angus Mackay

You can put a select statement inside the IN expression

SELECT *
FROM Table1
WHERE SomeColumn IN (SELECT Something FROM Table2)

And no need for pesky dynamic SQL.

Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

Colin Angus Mackay

He wanted to use the result of another SELECT statement inside the IN() expression which is a valid thing to do.

Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

_AK_

Actually what she has given was some values in a variable thats why I had given that type of solution.

Best Regards, Apurva Kaushal

_AK_

for this no need to go for the dynamic query you can directly use the method suggested by Colin.

Best Regards, Apurva Kaushal

Colin Angus Mackay

Apurva Kaushal wrote:

Actually what she has given was some values in a variable thats why I had given that type of solution

But if you read the pseudo sql that she provided you can see what she is trying to do:

declare @list nvarchar(1000)
set @list = select words from sample
select * from emp where dept in(@list)

The second line, being an apparent result from a SELECT statement gives the indication that it isn't necessarily a variable she wants, but the result of the SELECT statement.

Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

_AK_

yep and there exactly I got confused and gave that solution. :(

Best Regards, Apurva Kaushal

Colin Angus Mackay

Apurva Kaushal wrote:

yep and there exactly I got confused and gave that solution.

Don't worry about it. It wasn't explained too well. You sometimes need to read something like that a couple of times to really understand what is wanted.

Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

Uma Kameswari

Yes we can put a select statement inside the IN expression but if the column contains a list of values separated by comma(,) then it will not work.now the table data is like this table1 col1 hyderabad delhi mumbai table2 col1 hyderabad,chennai,bangalore bangalore,kolkata,delhi bangalore mumbai,hyderabad now if I give select * from table1 where col1 in (select col1 from table2) i will not get the output can u checkl it once Regards, Uma

Mark J Miller

You can create a table-valued function which uses a loop to split the csv list in table2 and insert them individually into a table variable. CREATE FUNCTION [dbo].[function_string_to_table] ( @string VARCHAR(MAX), @delimiter CHAR(1) ) RETURNS @output TABLE( data VARCHAR(256) ) BEGIN DECLARE @start INT, @end INT SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) WHILE @start < LEN(@string) BEGIN IF @end = 0 SET @end = LEN(@string) + 1 INSERT INTO @output (data) VALUES (SUBSTRING(@string, @start, @end - @start)) SET @start = @end + 1 SET @end = CHARINDEX(@delimiter, @string, @start) END RETURN END Then you can do this: DECLARE @values VARCHAR(MAX) SELECT @values = col1 FROM table2 WHERE FOO = 'BAR' SELECT * FROM table1 WHERE col1 IN ( SELECT data FROM dbo.function_string_to_table(@values, ',') )

_AK_

Colin Angus Mackay wrote:

You sometimes need to read something like that a couple of times to really understand what is wanted.

Yep that is correct.. Thanks for the suggestion. :)

Best Regards, Apurva Kaushal

Uma Kameswari

Yes we can put a select statement inside the IN expression but if the column contains a list of values separated by comma(,) then it will not work.now the table data is like this.I hope this will make you clear with my requirement. table1 col1 hyderabad delhi mumbai table2 col1 hyderabad,chennai,bangalore bangalore,kolkata,delhi bangalore mumbai,hyderabad now if I give select * from table1 where col1 in (select col1 from table2) i will not get the output can u check it once Regards, Uma

Colin Angus Mackay

Uma Kameswari wrote:

Yes we can put a select statement inside the IN expression but if the column contains a list of values separated by comma(,) then...

...it breaks the first normal form of database normalisation. The easiest solution from this point is to go to the dynamic SQL and use cursors to navigate through the rows. However, that is a very slow solution. If you can, it would be better if you could find some way of normalising the data so that it can be used more efficiently.

Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

Colin Angus Mackay

Now it looks like we were both a little bit correct and a little bit wrong. :) C'est la vie!

Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

M H 1 2 3

http://www.projectdmx.com/tsql/sqlarrays.aspx[^] You should be able to use CharIndex as the article in the link above suggests as one solution. select t1.* from table1 t1 join table2 t2 on charindex(',' + t1.col1 + ',' , ',' + t2.col1 + ',') > 0