Need SQL Query - 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 2005 Forums

Transact-SQL (2005)

Need SQL Query

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-03 : 04:27:40

i have a table in my database which maps two tables products and category
the table has three columns
1...id
2...product_id
3...category_id
a product can be associated with many categories

the query should take a list of category_id as input and output the distinct product_id which are associated with all of these categories.

eg
in my table i have the following data

id product_id category_id
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 4
7 2 5

now if i give 1,2,3 as input to query it should output me product_id = 1
if i give 1,2 as input to query it should output me product_id = 1,2

Vaibhav T

If I cant go back, I want to go fast...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-03 : 04:29:04

Sample Data and Query what I tried -
Is there any other way to do this -

Create table #Product (id int primary key,product_id int,category_id int)
insert #Product
select 1,1,1
union all
select 2,1,2
union all
select 3,1,3
union all
select 4,2,1
union all
select 5,2,2
union all
select 6,2,4
union all
select 7,2,5

DECLARE @Catid1 int,
@Catid2 int,
@Catid3 int
SELECT @Catid1=1,@Catid2=2,@Catid3=3
(SELECT distinct product_id
FROM #Product
where category_id=@Catid1)
intersect
(SELECT distinct product_id
FROM #Product
where category_id=@Catid2)
intersect
(SELECT distinct product_id
FROM #Product
where category_id=@Catid3)

Vaibhav T

If I cant go back, I want to go fast...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 02:09:16

Any suggestion ?

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources