Quest for an SQL Query to solve the below problem

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)

Quest for an SQL Query to solve the below problem

Author

Topic

joshymraj
Starting Member

5 Posts

Posted - 2007-02-21 : 02:23:13

Hello guys i got a problem when i tried to retrieve a value that has multiple match with different rows.I got a table with fields plan_id and feature_no,of which plan_id are numbers(1,2,3...) and feature_no are also nums(1,2,3...).For each plan id there are multiple features.
Looks like
plan_id feature_id
1 2
1 4
1 6
1 8
2 4
2 5
3 4
3 6
3 8
I want to retrieve plan_id which includes selected features.For eg.
i want plan_ids with features 4,6,8 so it should return plan_ids 1 and 3.Is there any solution for this.Plz help me guys

joshymraj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 02:49:03

[code]-- prepare sample data
declare @s table (plan_id int, feature_id int)

insert @s
select 1, 2 union all
select 1, 4 union all
select 1, 6 union all
select 1, 8 union all
select 2, 4 union all
select 2, 5 union all
select 3, 4 union all
select 3, 6 union all
select 3, 8

-- Show the expected output
SELECT Plan_ID
FROM @s
WHERE Feature_ID IN (4, 6, 8)
GROUP BY Plan_ID
HAVING COUNT(DISTINCT Feature_ID) = 3[/code]

Peter Larsson
Helsingborg, Sweden

joshymraj
Starting Member

5 Posts

Posted - 2007-02-21 : 03:10:22

Thank you very much Peso.It worked !I am very very happy now coz i m in the middle of a web project and was thinking to change the design coz of this problem.This query exactly suits my use.

joshymraj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 03:46:23

Good luck!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources