How to use in operator
-
He wanted to use the result of another
SELECTstatement inside theIN()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
-
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
-
Actually what she has given was some values in a variable thats why I had given that type of solution.
Best Regards, Apurva Kaushal
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
SELECTstatement gives the indication that it isn't necessarily a variable she wants, but the result of theSELECTstatement.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
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
SELECTstatement gives the indication that it isn't necessarily a variable she wants, but the result of theSELECTstatement.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
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
-
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
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
-
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
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 ENDThen 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, ',') ) -
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
-
for this no need to go for the dynamic query you can directly use the method suggested by Colin.
Best Regards, Apurva Kaushal
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
-
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
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 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
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
-
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
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