July 3, 2012 at 8:05 am
Suppose I have two tables. Students and StudentTopTwoChoices.
Students has a primary key, StudentID, and a lot of other information.
StudentTopTwoChoices has two columns in it, StudentID & FavoriteSubject. Each student can have up to 2 favorite subjects. So as an example, a populated StudentTopTwoChoices table could look like this.
StudentIDFavoriteSubject
1Subject1
1Subject2
2Subject1
2Subject3
3Subject2
3Subject4
Now, I need a query, where I pass on a list of Subjects, and the minimum number of students who have taken up all the subjects listed.
Examples.
Example#INPUTOUTPUT
1'Subject1,Subject2'All details for StudentID 1
2'Subject1,Subject3'All details for StudentID 2
3'Subject1,Subject4'All details for StudentIDs 1 & 3.
I tried
SELECT *
FROM Students
WHERE StudentID IN (
SELECT DISTINCT(StudentID)
FROM StudentTopTwoChoices
WHERE FavoriteSubject IN ('INPUT_SET_AS_LIST_OF_VALUES')
)
but it doesn't seem to work in all cases. The above query works fine for example #3, but for example #1 & #2, it returns details of both the student ids (1 & 2) whereas it should return only 1 of them.
Any thoughts?
July 3, 2012 at 9:15 am
You will probably need an IF..ELSE block to achieve this
IF EXISTS(
SELECTStudentId
FROMStudentTopTwoChoices
WHEREFavoriteSubject IN ('INPUT_SET_AS_LIST_OF_VALUES')
GROUP BY StudentId
HAVING COUNT(*) > 1
)
BEGIN
SELECT*
FROMStudents
WHEREStudentID IN (
SELECTStudentId
FROMStudentTopTwoChoices
WHEREFavoriteSubject IN ('INPUT_SET_AS_LIST_OF_VALUES')
GROUP BY StudentId
HAVING COUNT(*) > 1
)
END
ELSE
BEGIN
SELECT*
FROMStudents
WHEREStudentID IN (
SELECTStudentID
FROMStudentTopTwoChoices
WHEREFavoriteSubject IN ('INPUT_SET_AS_LIST_OF_VALUES')
)
END
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 3, 2012 at 11:22 pm
This doesn't quite work. In the event when the table has the following entries,
StudentIDFavoriteSubject
1Subject1
1Subject2
2Subject1
2Subject2
When I run the query, I should only get all details of StudentID 1, but I get all details of both StudentID 1 & 2. My requirement is to fetch the minimum number of students who have a list of subjects listed as their favorites. If there is 1 student who has all the listed subjects as a favorite, then the query should choose that 1 student alone.
July 4, 2012 at 12:44 am
What is the maximum number of subjects that will be passed in the 'INPUT_SET_AS_LIST_OF_VALUES'
Will it be always 2 or can it be more than 2 ?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 4, 2012 at 1:04 am
It can be more than 2.
July 4, 2012 at 1:55 am
You will probably need a CURSOR then to check all possible combinations and select the minimum one
I am unable think of a set-based solution right away, will let you know if i get any ideas.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 4, 2012 at 2:20 am
Thanks! Will watch this space.
July 4, 2012 at 6:23 am
From that:
Kaptnik (7/3/2012)
... Each student can have up to 2 favorite subjects...
and that:
...need a query, where I pass on a list of Subjects, and the minimum number of students who have taken up <b>all</b> the subjects listed...
in case of:
...
What is the maximum number of subjects that will be passed in the 'INPUT_SET_AS_LIST_OF_VALUES'
Will it be always 2 or can it be more than 2 ? ...
It can be more than 2.
When supplied more than 2 subjects in a list, your query will return no records as per number 1 "student can have up to 2 favorite subjects" will be no student who taken up more than 2 subjects.
Anyway, I cannot see why would you need a cursor here...
Is it a home work?
July 4, 2012 at 6:35 am
The sample data provided is not that extensive
Consider the below scenario
DECLARE @tbl_Student TABLE
(
StudentID INT,
FavoriteSubject VARCHAR(50)
)
INSERT @tbl_Student( StudentID, FavoriteSubject )
SELECT 1, 'Subject1' UNION ALL
SELECT 1, 'Subject2' UNION ALL
SELECT 2, 'Subject3' UNION ALL
SELECT 2, 'Subject4' UNION ALL
SELECT 3, 'Subject5' UNION ALL
SELECT 3, 'Subject4' UNION ALL
SELECT 4, 'Subject1' UNION ALL
SELECT 4, 'Subject3' UNION ALL
SELECT 5, 'Subject2' UNION ALL
SELECT 6, 'Subject3' UNION ALL
SELECT 6, 'Subject1' UNION ALL
SELECT 7, 'Subject5'
If I pass 'Subject1','Subject2','Subject3','Subject4' in the filter, I should get records for Student Id's 1 & 2 only as they have all the subjects covered
@Kaptnik: I hope my assumption is correct, correct me if I am wrong
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply