SP for filters with multiple values

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)

SP for filters with multiple values

Author

Topic

cda2007
Starting Member

17 Posts

Posted - 2010-03-11 : 18:35:43

I have a table with 3 columns that make the primary key:
Product_ID
CharacteristicName_ID
Value_ID

One product has multiple many referring rows.

I want to make a SP that returns the Product_IDs depending on multiple values.
It's been one week so far since I've been trying to achieve this.

Please help me!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-11 : 19:44:44

What values (inputs) are you looking to use? What records (outputs) are you expecting?

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

cda2007
Starting Member

17 Posts

Posted - 2010-03-12 : 03:31:51

Lets say in that table I have this data:
Product_ID CharecteristicName_ID Value_id
1 2 1
1 3 7
1 5 10
2 2 2
2 3 7
2 5 10
3 2 2

I want it to return Product_ID 1, 2 when i enter the values 7 and 10.
One product can have a hundred or one rows.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 03:41:34

quote:
Originally posted by cda2007

Lets say in that table I have this data:
Product_ID CharecteristicName_ID Value_id
1 2 1
1 3 7
1 5 10
2 2 2
2 3 7
2 5 10
3 2 2

I want it to return Product_ID 1, 2 when i enter the values 7 and 10.
One product can have a hundred or one rows.

So what is the expected result for above data ?

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

cda2007
Starting Member

17 Posts

Posted - 2010-03-12 : 03:49:28

A table with 1 column 2 rows:
Product_ID
1
2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 03:53:14

both the value 7 and 10 must exists for the Product_ID ?


select Product_ID
from   atable
where  Value_ID in (7, 10)
group by Product_ID
having count(*) = 2

or just any one


select Product_ID
from   atable
where  Value_ID in (7, 10)
group by Product_ID

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

cda2007
Starting Member

17 Posts

Posted - 2010-03-12 : 04:12:51

The first one is the best. I can provide a list separated by commas.
Is there any way to count the members in a comma separated list?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-12 : 04:15:53

SELECT COUNT(1) FROM atable where value_ID IN (7,10,.......)

Vabhav T

cda2007
Starting Member

17 Posts

Posted - 2010-03-12 : 04:43:31

I want to do this:

Declare @list

select Product_ID
from atable
where Value_ID in (@list)
group by Product_ID
having (number of id's in @list)

I've found a tutorial at one point but as it happens I can't find it now.
I remember it was something with a function that splits on commas.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-12 : 05:00:57

quote:
Originally posted by cda2007

I want to do this:

Declare @list

select Product_ID
from atable
where Value_ID in (@list)
group by Product_ID
having (number of id's in @list)

I've found a tutorial at one point but as it happens I can't find it now.
I remember it was something with a function that splits on commas.

What does it mean by
having (number of id's in @list)
in the above query
this is not valid statement having (any number)

Vabhav T

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-12 : 05:03:21

quote:
Originally posted by cda2007

I want to do this:

Declare @list

select Product_ID
from atable
where Value_ID in (@list)
group by Product_ID
having (number of id's in @list)

I've found a tutorial at one point but as it happens I can't find it now.
I remember it was something with a function that splits on commas.

See this thread.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

cda2007
Starting Member

17 Posts

Posted - 2010-03-12 : 06:27:08

I managed to work around the last part: Having count(*) = (Select count(*) from dbo.Split(@list, ','))

But I can't figure out how to parse @list to work in the Where clause:
Where Value_ID in (@list)

How do I do it?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-12 : 09:32:51

Go by dynamic query for that or
join the result set of (Select <columnname> from dbo.Split(@list, ','))) with the value_id

Vabhav T

cda2007
Starting Member

17 Posts

Posted - 2010-03-12 : 16:33:15

I've been looking for a dynamic mode but I didn't find a way to add the column values to the IN clause.
Does anyone know a way?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 19:00:44

quote:
Originally posted by cda2007

I managed to work around the last part: Having count(*) = (Select count(*) from dbo.Split(@list, ','))

But I can't figure out how to parse @list to work in the Where clause:
Where Value_ID in (@list)

How do I do it?

where value_id in (Select split_value from dbo.Split(@list, ','))

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 02:17:25

or

where ','+@list+',' LIKE '%,' + cast(value_id as varchar(20)) + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-13 : 03:45:46

Hey visakh there might be possible the value is 11 and in the list some where value is 111 there this might give unexpected result. is it or not?

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 04:03:18

quote:
Originally posted by vaibhavktiwari83

Hey visakh there might be possible the value is 11 and in the list some where value is 111 there this might give unexpected result. is it or not?

Vabhav T

it wont. notice the ,'s preceding and succeeding the values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-13 : 04:14:53

quote:
Originally posted by visakh16

quote:
Originally posted by vaibhavktiwari83

Hey visakh there might be possible the value is 11 and in the list some where value is 111 there this might give unexpected result. is it or not?

Vabhav T

it wont. notice the ,'s preceding and succeeding the values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ohh, sorry I missed that...

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 04:19:39

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cda2007
Starting Member

17 Posts

Posted - 2010-03-13 : 05:14:42

Thank you so, so much. You have ended a week and 1 day of testing, stored, dynamic procedures with 30 parameters.
I can enjoy my weekend.
Thank you again.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources