Hello all. I inadvertently sent some of my replies on Friday to
individuals and not back to the nug. Whoops. Thanks for all of the
replies. I've gotten some great help from the nug. I've learned a few
cool things, but I haven't yet figured out my original problem.
Here's the situation:
Table: class Columns: name, id (both VARCHAR)
Table: student - Columns: name, id (both VARCHAR)
Table link - Columns: pid, cid (both VARCHAR) This table links
students and classes
Class A has Sean, Narinder, Max, Norman
Class B has Mary, Jane, Narinder, Norman
Class C has Sean, Sally
Here's what I have working so far. I'm able to get every student who's
in EITHER class A or class B. using this code:
SELECT DISTINCT person.name FROM person, class, link WHERE
link.pid=person.id AND link.cid=class.id AND class.id IN ("A", "B")
Results: Sean, Narinder, Max, Norman, Mary, Jane
Or using this code:
SELECT DISTINCT person.name FROM person, class, link WHERE
link.pid=person.id AND link.cid=class.id AND (class.id = "A" OR
class.id = "B")
Same Results: Sean, Narinder, Max, Norman, Mary, Jane
I can also get any student who is in more than one class (but not
class specific) using this code:
SELECT person.name FROM person WHERE (SELECT COUNT(*) FROM link WHERE
link.pid=person.id)>1
Results: Sean, Narinder, Norman
Or, similarly, I can also get any student who is in exactly two
classes (but not class specific) using this code (only change >1 to
=2):
SELECT person.name FROM person WHERE (SELECT COUNT(*) FROM link WHERE
link.pid=person.id)=2
Results: Sean, Narinder, Norman
What I'm still after is only students in a specific set of classes.
For example - A & B. (A recordset containing only Narinder and Norman
is what i'd want - They are the only two students who are in both
classes A & B) I've tried various forms of GROUP BY and COUNT, but I
haven't been able to get any results.
Learning is fun. I feel I'm close. Hopefully, this will help people in
the future as well since this kind of structure is used so often in
databases: actors and movies, songs and artists, etc.
Thanks,
--
Sean McCollum
Mac OS 10.4.7
RB 2006 r3 Pro
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
|