Sean,
Hi. OK, I think I got it. You need to use aggregates. How about this, which
returns the required rows (Norman, Narinder who appear 2 times which the
Having clause will filter to only include) :
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(*) > 1
Regards,
Narinder.
--
___________________________________________
| Narinder Chandi,
| ToolBox Systems Limited,
| Surrey, England, UK.
| tel/fax : +44 (0)1372 720117
| mobile : +44 (0)7973 512495
| skype : NarinderChandi
| www : http://www.toolbox.uk.com
|___________________________________________
| Consultancy * Development * Support
| * 4D Solutions Partner since 1998 *
|___________________________________________
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>
