comma seperated parameter - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

comma seperated parameter

Author

Topic

naveentnk
Starting Member

15 Posts

Posted - 2009-02-19 : 12:03:24

i have 3 tables

1. customer_master(customerid, customername)
2. language_master(languageid, languagename)
3. customer_language(customerid, languageid)

i will get a parameter like 'english, kannada, punjabi' to my stored procedure or query. how simple can I get all the customers who speak all the 3 languages.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 12:13:24

[code]
SELECT c.customername
FROM customer_master c
INNER JOIN
(
SELECT cl.customerid,COUNT(DISTINCT l.languagename) AS langcnt
FROM customer_language cl
ON cl.customerid=c.customerid
INNER JOIN language_master l
ON l.languageid=cl.languageid
INNER JOIN (SELECT f.Val FROM dbo.ParseValues(@param,',')f)t
ON t.Val=l.languagename
GROUP BY cl.customerid
)tmp
ON tmp.customerid=c.customerid
WHERE langcnt=(SELECT COUNT(DISTINCT f1.Val) FROM dbo.ParseValues(@param,',')f1)
[/code]

parsevalues can be found here

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

naveentnk
Starting Member

15 Posts

Posted - 2009-02-20 : 04:51:53

Thanks for the reply. But i am getting the following error.

Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'ON'.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near 't'.

naveentnk
Starting Member

15 Posts

Posted - 2009-02-20 : 04:56:35

Guys any one out there to give me a better option?

Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-20 : 05:20:49

try this

slight modification to visakh solution

SELECT c.customername
FROM customer_master c
INNER JOIN
(
SELECT cl.customerid,COUNT(DISTINCT l.languagename) AS langcnt
FROM customer_language cl

INNER JOIN language_master l
ON l.languageid=cl.languageid
INNER JOIN (SELECT f.Val FROM dbo.ParseValues(@param,',')f)t
ON t.Val=l.languagename
where cl.customerid=c.customerid
GROUP BY cl.customerid
)tmp
ON tmp.customerid=c.customerid
WHERE langcnt=(SELECT COUNT(DISTINCT f1.Val) FROM dbo.ParseValues(@param,',')f1)

parsevalues can be found here

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

naveentnk
Starting Member

15 Posts

Posted - 2009-02-20 : 06:14:12

I am getting the following error when running ur query.

Server: Msg 107, Level 16, State 2, Line 3
The column prefix 'c' does not match with a table name or alias name used in the query.

Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 06:21:27

slight modification try this one
SELECT c.customername
FROM customer_master c
INNER JOIN
(
SELECT cl.customerid,COUNT(DISTINCT l.languagename) AS langcnt
FROM customer_language cl

INNER JOIN language_master l
ON l.languageid=cl.languageid
INNER JOIN (SELECT f.Val FROM dbo.ParseValues(@param,',')f)t
ON t.Val=l.languagename
~~where cl.customerid=c.customerid~~
GROUP BY cl.customerid
)tmp
ON tmp.customerid=c.customerid
WHERE langcnt=(SELECT COUNT(DISTINCT f1.Val) FROM dbo.ParseValues(@param,',')f1)

naveentnk
Starting Member

15 Posts

Posted - 2009-02-20 : 09:59:15

Thanks for the reply. But i am not getting the result. ie in the results i am not getting any customer. here is my table structure and values in those tables

create table #customer_master(customerid int, customer varchar(100))
insert into #customer_master select 1, 'abc' union select 2, 'bcd' union 3, 'xyz'

create table #language_master(languageid int, languagename varchar(100))
insert into #language_master select 1, 'telugu' union select 2,'english' union select 3,'hindi'
union select 4,'tamil' union select 5, 'kannada'

create table #customer_language(customerid int, languageid int)
insert into #customer_language select 1,1 union select 1, 3 union select 1, 5
union select 2,2 union select 2, 4 union select 3, 3 union select 3, 5 union select 3,1

my @param value is 'hindi, telugu'

I should get the abc and xyz customernames as the result when i run the query because abc and xyz are both speaking 'hindi' and 'telugu' languages.

Anybody to help me out?

Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 10:25:22

show the query used

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 23:09:32

[code]
try this ur getting output as
create table #customer_master(customerid int, customer varchar(100))
insert into #customer_master select 1, 'abc' union select 2, 'bcd' union select 3, 'xyz'

create table #language_master(languageid int, languagename varchar(100))
insert into #language_master select 1, 'telugu' union select 2,'english' union select 3,'hindi'
union select 4,'tamil' union select 5, 'kannada'

create table #customer_language(customerid int, languageid int)
insert into #customer_language select 1,1 union select 1, 3 union select 1, 5
union select 2,2 union select 2, 4 union select 3, 3 union select 3, 5 union select 3,1
declare @param varchar(32)
select @param = 'hindi, telugu'

SELECT c.customer
FROM #customer_master c
INNER JOIN
(
SELECT cl.customerid,COUNT(DISTINCT l.languagename) AS langcnt
FROM #customer_language cl
INNER JOIN #language_master l
ON l.languageid=cl.languageid
INNER JOIN (SELECT ltrim(f.Val) as val FROM dbo.ParseValues(@param,',')f)t
ON t.Val=l.languagename
GROUP BY cl.customerid
)tmp
ON tmp.customerid=c.customerid
WHERE langcnt=(SELECT COUNT(DISTINCT f1.Val) FROM dbo.ParseValues(@param,',')f1)
output as
customer
--------------
abc
xyz[/code]

naveentnk
Starting Member

15 Posts

Posted - 2009-02-21 : 02:01:13

Thanks a lot. Its working.

Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-21 : 02:09:15

quote:
Originally posted by naveentnk

Thanks a lot. Its working.

Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources