How to filtered Multiple ID (with comma separated

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)

How to filtered Multiple ID (with comma separated

Author

Topic

anwarul.haque
Starting Member

7 Posts

Posted - 2009-06-26 : 01:05:47

Hi all,

Here are my problem with SQL Query.

Here are Tables A and B

Below are fields of both table A & B

Fields contains values

A.LocationID="1,3,5,7,9"

B.LocationID="1,2,6,10"

I need such query

Example

Select A.LocationID from A

Where A.ID=@ID AND A.LocationID in (Select B.LocationID from B WHER B.ID=@ID)

Is there any other way that I can put LIKE instead of IN with sub query?
In this case only 1 value matched from Table A, so it return at lest one row.
How can I do in SQL Query?
If any single id matched or more id matched with table B then return a row.
Any ideas will be greatly appreciated.
Thanks in Advance

Haque

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-26 : 01:26:18

see these examples
select empid,empsal from emptable where '%,' + '1,2,5'+ ',%' LIKE '%,' + CAST( empid AS VARCHAR(255)) +',%'

exec('select empid,empsal from emptable where cast(empid as varchar) in('+'1,2,5'+') ')

select empid,empsal from emptable where cast(empid as varchar) in('1,2,5')
or use patindex function
post some sample data and expected output

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-26 : 02:02:27

quote:
Originally posted by bklr

see these examples
select empid,empsal from emptable where '%,' + '1,2,5'+ ',%' LIKE '%,' + CAST( empid AS VARCHAR(255)) +',%'

exec('select empid,empsal from emptable where cast(empid as varchar) in('+'1,2,5'+') ')

select empid,empsal from emptable where cast(empid as varchar) in('1,2,5')
or use patindex function
post some sample data and expected output

This will not work

select empid,empsal from emptable where cast(empid as varchar) in('1,2,5')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-26 : 02:37:38

[code]
select distinct a.LocationID, b.LocationID
from (
select a.LocationID, al.stringval
from @A a
cross apply dbo.CSVTable(a.LocationID) al
) a
inner join
(
select b.LocationID, bl.stringval
from @B b
cross apply dbo.CSVTable(b.LocationID) bl
) b on a.stringval = b.stringval
[/code]

KH
[spoiler]Time is always against us[/spoiler]

anwarul.haque
Starting Member

7 Posts

Posted - 2009-06-26 : 04:54:04

quote:

Originally posted by khtan


select	distinct a.LocationID, b.LocationID
from	(
		select	a.LocationID, al.stringval
		from	@A a
			cross apply dbo.CSVTable(a.LocationID) al
	) a
	inner join
	(
		select	b.LocationID, bl.stringval
		from	@B b
			cross apply dbo.CSVTable(b.LocationID) bl
	) b	on	a.stringval	= b.stringval

KH
[spoiler]Time is always against us[/spoiler]

Thnak you very much.
Let you know after applying this method.

Haque

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-26 : 04:59:42

everything will be much easier if your table are normalized.

KH
[spoiler]Time is always against us[/spoiler]

saran_d28
Starting Member

36 Posts

Posted - 2009-06-26 : 06:13:00

Hi,

Select a.location_id, b.location_id
from a
join b
on instr(a.location_id, b.location_id) > 0

--Saravanan

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-26 : 06:17:18

quote:
Originally posted by saran_d28

Hi,

Select a.location_id, b.location_id
from a
join b
on instr(a.location_id, b.location_id) > 0

--Saravanan

wt is instr? is this in Mssql

anwarul.haque
Starting Member

7 Posts

Posted - 2009-06-26 : 06:30:50

quote:
Originally posted by bklr

quote:
Originally posted by saran_d28

Hi,

Select a.location_id, b.location_id
from a
join b
on instr(a.location_id, b.location_id) > 0

--Saravanan

wt is instr? is this in Mssql

instr equal to in SQL CHARINDEX
Thats Great. Its working for me & lts so easy.
Thank you very much.

Haque

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 14:09:18

quote:
Originally posted by anwarul.haque

quote:
Originally posted by bklr

quote:
Originally posted by saran_d28

Hi,

Select a.location_id, b.location_id
from a
join b
on instr(a.location_id, b.location_id) > 0

--Saravanan

wt is instr? is this in Mssql

instr equal to in SQL CHARINDEX
Thats Great. Its working for me & lts so easy.
Thank you very much.

Haque

so are you not using sql server?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-26 : 16:14:29

quote:
Originally posted by visakh16

quote:
Originally posted by anwarul.haque

quote:
Originally posted by bklr

quote:
Originally posted by saran_d28

Hi,

Select a.location_id, b.location_id
from a
join b
on instr(a.location_id, b.location_id) > 0

--Saravanan

wt is instr? is this in Mssql

instr equal to in SQL CHARINDEX
Thats Great. Its working for me & lts so easy.
Thank you very much.

Haque

so are you not using sql server?

ORACLE

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources