Extract entities having ALL of a set of attributes
-
Dear Group,
I'm looking for a set-oriented way to ask the following question: Give me the list of students who are taking BOTH trigonometry and Theatre I. Variations on this query would include a longer list of courses.
Student table contains StudentID, Name, and demographic info. StudentCourses table contains StudentID and CourseID. Courses table contains CourseID and CourseDescription.
I was able to do this sort of query easily in the old PC-based Paradox database. I thought that the ALL keyword in T-SQL might help here, but it doesn't.
I can put together a query that gives me the results I want with a bunch of subqueries, each one getting the set of students taking one of the requested courses...but it seems that there should be a set-oriented way to define the list of courses and then extract the students that take EVERY course from the list. Kind of like the IN keyword...but extended to mean "EVERY ONE IN".
Any ideas?
Thank you!
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
You could try something like this:
select distinct StudentID, Name
from (
select StudentID, Name,
Trigonometry = case sc.CourseDescription when 'Trigonometry' then 1 else 0 end,
TheatreI = case sc.CourseDescription when 'Theatre I' then 1 else 0 end
from Student s
inner join StudentCourses sc on s.StudentID = sc.StudentID
inner join Courses c on c.CourseID = sc.CourseID
-- where sc.CourseDescription in ('Trigonometry', 'Theatre I')
) d where Trigonometry = 1 and TheatreI = 1
-
Paul,
Hmmm...that query doesn't quite do the trick. If the student takes both courses, the subquery produces a separate row for each course, with the 1/0 flags set separately. That is, Trig is flagged 1 in one of the rows while TheatreI is flagged 1 in the other row for the student that takes both courses.
Thus when we get down to the final WHERE clause, there is no single record in the subquery result set that contains both flags set to 1.
I DID get a reply from another SQL forum on this topic:
SELECT S.EmployeeID, Student.Name
FROM Student AS S
INNER JOIN
(SELECT DISTINCT StudentID, Count(StudentID)
FROM StudentCourses AS C
WHERE CourseID IN (4,7) and Count(StudentID) = 2
GROUP BY StudentID)
ON S.StudentID = C.StudentID
This seems to work. I'll just have to keep looking for a set-oriented approach like the old Paradox database.
Thanks, Paul, for giving this a shot.
Sincerely,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
You can find a way to handle the same kind of problem in this thread here http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=253037#bm253123
(how to return only those who have all items in an IN list qualified)
/Kenneth
-
you can use the following query ?
select * from
student s join studentcourse sc
on <join condition>
join
course c
on <join condition>
-
Joe,
That's IT! You know, I even checked out your second edition of "SQL for Smarties" from the library not long ago, but I forgot all about relational division.
In looking back through SQLServerCentral posts I found this interesting link by a professor in my neck of the woods:
http://www.cs.arizona.edu/people/mccann/research/divpresentation.pdf
He presented FOUR SQL methods:
1) Direct conversion of the Relational Algebra expression
2) By applying a quantification tautology
3) By using set containment
4) By comparing set cardinalities
Methinks the ones using the EXCEPT keyword (#1 and #3) don't cut the mustard in SQL Server.
Thank you for the shove in the right direction.
Regards,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
Kenneth,
Thank you very much. A nice discussion of the problem. I found one of Joe's posts that describes things in a similar way.
Regards,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
Amit,
That doesn't quite do the trick either, I'm afraid. It joins the two "lookup" tables to the many-to-many table, StudentCourse, but it makes no provision for selecting only those students that have taken a subset of courses. Messrs. Celko and Wilhelmsson (our friend from Iceland?) steered me in the right direction. Thanks.
Regards,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
Close enough
When looking out the window, I'd imagine that the weather could be the same in Iceland as here, though I live more eastward, in Sweden.
Still, it's among the Nordic countries, so the guess wasn't too far off
/Kenneth
-
Kenneth,
Sorry about that passing reference. Your name looked very Icelandic to me and so I took a flyer. My experience with/knowledge of Scandinavian countries extends mostly to Iceland where Bobby Fischer played Boris Spassky in 1972 and where the alcoholism rate is quite high. I also used the example of the Icelandic patronymic naming of children to illustrate to a database class that Icelandic phone books are listed by FIRST name.
Er tillgivne,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
Ah, no problem at all Steven.
I've never been to Iceland, though I'd like to visit there sometime.
From what I (think) I know about Icelandic naming standards, is that what we pass as 'first name' is your name - what we pass as 'last name', is actually not a name at all, but it tells 'who you are' (not what you are called)
Maybe someone from the island can confirm if I got it right or not..?
/Kenneth
-
Kenneth,
I got this from Wikipedia:
"Members of other cultures often find it unusual that Icelanders formally address others by their first name. For example, current prime minister Halldór Ásgrímsson would not be addressed as Ásgrímsson or Mr. Ásgrímsson by another Icelander; he would either be addressed only by his first name (or first and second if he had one), or his full name. The cultural meaning of an Icelander's last name is not that it is a part of one's name, but a short description of who one is. Halldór is Ásgrímsson — a son of Ásgrímur. Legally, it is a part of his name. Culturally, it is a definition of who begat whom, even if that definition is seemingly vague.
"One consequence of this is that in Iceland, directories of people's names, such as the phone directory, are alphabetised by given name, not by surname."
Reminds me of reading novels translated from Russian (or spy novels with Russians) in which the characters say, "You're looking pretty good, Yuri Stepanovich!" "You're not looking so bad yourself, Ivan Grigorievich!"
I had to use that joke. Garrison Keillor did a live broadcast in Milwaukee, Wisconsin, (not too far from where I live) and he told the audience that the state's nickname, the Badger State, came from the above type of friendly exchange: "You're not looking so BADGER self." Sorry. Couldn't resist.
Regards,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply