Multiple subqueries?

thf1977 · October 6th, 2006, 06:06 AM

I'm having a bit of a problem with a MySql query to (I think) three tables. I "inherited" the system from another, somewhat more experienced MySQL programmer, so I don't find it all that easy to navigate in.

Problem is this:

I have three tables, A, B and C.

I want the number of occurrences (Count) in Table A of Person = X. But it must be conditioned by a certain type of events. In Table A is an A.Eventno (corresponding to B.IDno). In Table B are the details for that event. One of the details is B.MasterEvent (corresponding to C.IDno). However, the details for the MasterEvent is in Table C, and the specific sub-types are in C.SubType.

What I want is all Person = X where the A.Eventno is of certain C.SubTypes.

Hope I'm making myself clear here.

I have read a bit about subqueries, but somehow the exact structuring for a problem like this one has escaped me until now...

Hope one of you can give me a hand with this.

-Thomas

Thomas Frandzen

ciderpunx · October 24th, 2006, 05:30 PM

Something like:

Code:

SELECT COUNT(person.id) 
  FROM A,B,C 
 WHERE person='X' 
   AND B.PersonID=A.id 
   AND B.MasterEvent=C.IDno 
   AND C.SubType IN ('type1','type2','type3');

?

--
Don't Stand on your head - you'll get footprints in your hair
http://charlieharvey.org.uk
http://charlieharvey.com