MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:January 15 2003 4:15pm
Subject:Re: three table join
View as plain text  
Let me give this a try. I've done 3 and 4 table joins, but  I did a
little trial and error before I got it right.
To break it down, you want to get three things:
1) All meetings that fall under a particular event
	select * from meetings where eid=2
2) All people id's that are part of that selected meeting list
	select * from meetings, mpeople
		where mpeople.mid=meetings.id and
	meetings.eid=2
3) Link the people names with the people id
	select * from meetings, mpeople, people
	where mpeople.mid=meetings.id and
		people.id=mpeople.id and
	meetings.eid=2

I think that might do it. Try it out. You only need to do a left join
(or right join) if you want to grab every record regardless of whether
or not there are related records. Like finding out how many people are
attending each meeting. You would want all meetings regardless of
whether or not they have zero people attending them.
Something like:
select meetings.id,count(mpeople.mid) as pcount
from mpeople right join meetings on mpeople.mid=meetings.id
group by mpeople.mid

If that doesn't work, let me know. I'm pretty sure it's close.

sql,query,queries,smallint


On Tuesday, January 14, 2003, at 07:37 PM, Josh L Bernardini wrote:

> ?How do I get the list of all the people in all the meetings given an 
> event
> id? Naturally the following doesn't work in 3.23.54.
>
> select firstname, lastname from people left join mpeople on people.id =
> mpeople.id where mpeople.mid IN (select meetings.id from meetings where
> eid=2);
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

Thread
three table joinJosh L Bernardini15 Jan
  • Re: three table joinMichael T. Babcock15 Jan
  • Re: three table joinBrent Baisley15 Jan
RE: three table joinTab Alleman15 Jan
  • Re: three table joinHarald Fuchs16 Jan
  • Re: three table joinMichael T. Babcock22 Jan
RE: three table joinDiana Soares15 Jan