gettting a list for an "in" query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

gettting a list for an "in" query

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-12-16 : 12:16:17

i have a query where i want to do :
"select...... where id in (XXXX)"
in the XXX i want to get a data from another table.
i thought of using a function which will give me the list for XXX.
the table from which i need to take the data is :
id,name
=======
1 xdf
2 ccc
3 ggg
so if for example i want do to:
select id from XXXX where name in ('xdf','ggg')
i will get in result :
1,3

how can i do this?
thanks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-16 : 12:18:20

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116307

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 12:54:34

[code]select id from XXXX where name in (select ....other query)[/code]

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-12-16 : 14:16:30

the thing is that i need it as an int list and not a string list
vishak16 - in the query i use it dosenlt let me put in the subqery

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 14:20:46

quote:
Originally posted by pelegk2

the thing is that i need it as an int list and not a string list
vishak16 - in the query i use it dosenlt let me put in the subqery

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

why? can you post your current query?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-16 : 23:17:33

SELECT STUFF((select ',' + convert(varchar(12) ,id) from xxxx where name in ('xdf','ggg') for xml path('')),1,1,' ')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 23:22:26

[code]DECLARE @List varchar(max)
SELECT @List=STUFF((select ',' + convert(varchar(12) ,id) from xxxx where name in ('xdf','ggg') for xml path('')),1,1,' ')

select id from XXXX where ','+@List+',' LIKE '%,'+name+',%' [/code]

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-12-17 : 05:07:35

bklr - this is the result that i get when using your syntax :

quote:
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

visakh16- this is the result that i get when using your syntax :

quote:
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value ' 32,30' to data type tinyint.

this is the query : (the @list is where is put your code)


SELECT     DM.CompanyRef, DM.CommandRef, SUM(CASE WHEN CRTM.EID IN (30, 32) THEN 1 ELSE 0 END) 
                      	AS d, SUM(CASE WHEN CRTM.EID NOT IN (@List) THEN 1 ELSE 0 END) AS u, 
                      	(CRTM.SD / 1000000) * 1000000
		FROM         CRTM   WITH (NOLOCK)     INNER JOIN
                      	DM  WITH (NOLOCK)  ON CRTM.MessageRef = DM.Id
		WHERE     (DM.LaP = 1)
		AND CRTM.SD BETWEEN 20080901000000 AND 20081231000000
		GROUP BY DM.CompanyRef, DM.CommandRef, (CRTM.SD / 1000000) * 1000000

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 05:21:51

[code]
SELECT DM.CompanyRef, DM.CommandRef, SUM(CASE WHEN CRTM.EID IN (30, 32) THEN 1 ELSE 0 END)
AS d,
SUM(CASE WHEN ','+@List+',' NOT LIKE '%,'+CAST(CRTM.EID AS varchar(50))+',%' THEN 1 ELSE 0 END) AS u,
(CRTM.SD / 1000000) * 1000000
FROM CRTM WITH (NOLOCK) INNER JOIN
DM WITH (NOLOCK) ON CRTM.MessageRef = DM.Id
WHERE (DM.LaP = 1)
AND CRTM.SD BETWEEN 20080901000000 AND 20081231000000
GROUP BY DM.CompanyRef, DM.CommandRef, (CRTM.SD / 1000000) * 1000000
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources