Sean,
Actually, I just realised my suggestion is flawed. It should be written :
SELECT person.name FROM person, class, link WHERE
link.pid=person.id AND link.cid=class.id AND class.name IN ('A', 'B')
GROUP BY person.name
HAVING count(*) = 2
So if you wanted to search across 3 classes, you'd write :
SELECT person.name FROM person, class, link WHERE
link.pid=person.id AND link.cid=class.id AND class.name IN ('A', 'B', 'C')
GROUP BY person.name
HAVING count(*) = 3
Regards,
Narinder.
on 28/7/06 19:34, Sean McCollum at [EMAIL PROTECTED] wrote:
> Thanks for all of the replies. I've been trying Narinder's code and I
> think I'm getting close. I added DISTINCT to the first of his SELECT
> statements and I'm able to get every student who's in EITHER class A
> or class B. Very close. That actually helps with another question I
> would have had, so thanks for the preemptive answer.
>
> Class A has Sean, Narinder, John, Norman
> Class B has Mary, Jane, Narinder, Norman
>
> Here's the 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, John, Norman, Mary, Jane
>
> What I'm still after is only students in both classes A & B. (Narinder
> and Norman - They are the only two students who are in both classes.)
>
> I tried Narinder's second line of code - it returns the same results,
> as he said, as the first select statement. So, I thought using AND
> instead of OR would work, but it didn't.
>
> 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")
>
> Results: Sean, Narinder, John, Norman, Mary, Jane
>
> I'll look at some of the other code, I haven't had a chance to try
> John's. Any thoughts?
_______________________________________________
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>
