> Message: 12 > Subject: SQL Select > From: "Sean McCollum" <[EMAIL PROTECTED]> > Date: Fri, 28 Jul 2006 09:43:05 -0500 > > Hello all, > > I hope some of you SQL wizards can help me figure this out: > > The statement below works perfectly. I get all student names > in the class with a class id of "A" > SELECT person.name FROM person, class, link WHERE > link.pid=person.id AND link.cid=class.id AND class.id="A" > > What I can't figure out is how to get the names of students > who are in TWO or more classes, "A" and "B", for example. In > English: "Select all students who are in class A as well as > class B". Or any combination of multiple classes: A&C, A,B&C, > etc. Can that be accomplished in one select statement? I > haven't been able to do that. > > db setup: > > Table: person > Columns: id, Name > > Table: class > Columns: id, Name > > Table: link > Columns: pid, cid > > Thanks for any guidance. I'm really stumped. > > -- > Sean McCollum > Mac OS 10.4.7 > RB 2006 r3 Pro
Hi Sean, I did a quick mock-up of your database in Microsoft Access here at work and this query gives you all of the people who are in more than one class and which class they are in. I'm not sure what database you are using so I can't say if this will work for you or not but it is fairly standard SQL (nothing too exotic). SELECT person.name, class.name FROM person, class, link where person.id in (SELECT link.pid FROM link GROUP BY link.pid HAVING Count(link.cid)>1) and link.pid=person.id AND link.cid=class.id; I'm not sure if this is exactly what you are looking for but hopefully will put you on the right track. Good luck. -John Sims _______________________________________________ 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>
