Thanks to everyone who answered my question.
With some further reading in the searchable archives at
http://www.listsearch.com/mysql.lasso, I managed to solve the problem on my
own last night and grow up to a 9 table join.
Tab, I think you just want two left joins instead of a left join and a
right join.
SELECT SomeStuff
FROM Table1 AS T1
LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1
LEFT JOIN Table3 AS T3 ON T2.FK2 = T3.PK
WHERE T1.PK=999
Now for those who understand a little better, what's the difference/when do
you neeed a right join, straight join, etc? Any examples would be helpful.
thanks, jb
PS - heres a 9 table left join. I guess you can join any number of tables
so long as there is a relationship between them.
select people.id, concat(lastname, ', ', firstname) as name, title,
organizations.org, ptype, groupname, meeting, room, location,
moderator,
addnotes, dtime, edtime
from people left join organizations on people.org=organizations.id
left join otype on organizations.otype=otype.id
left join pgroups on people.id=pgroups.pid
left join groups on pgroups.gid=groups.id
left join mgroups on pgroups.gid=mgroups.gid
left join meetings on mgroups.mid=meetings.id
left join rooms on meetings.rid=rooms.id
left join locations on rooms.lid=locations.id
left join events on meetings.eid=events.id
where events.id=2 order by meeting \G
|---------+---------------------------->
| | "Tab Alleman" |
| | <Tab.Alleman@Metr|
| | oGuide.com> |
| | |
| | 01/15/2003 08:53 |
| | AM |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: <[EMAIL PROTECTED]>
|
| cc:
|
| Subject: RE: three table join
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
Nice that this came up when it did.. I'm currently struggling with a
three-table join.
Table1.PK = Table2.FK1
Table3.PK = Table2.FK2
My last effort looks something like:
SELECT SomeStuff
FROM Table1 AS T1
LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1
RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK
WHERE T1.PK=999
I want it to return 1 row, but it's returning as many rows as there are
in Table3. Where am I goofing?
TIA,
Tab
mysql
-----Original Message-----
From: Michael T. Babcock [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 15, 2003 8:30 AM
To: Josh L Bernardini
Cc: [EMAIL PROTECTED]
Subject: Re: three table join
I've wanted to post this query example a few times (and I hope I got it
right; mornings aren't my best time) ... multiple JOINs:
SELECT stuff
FROM table1
LEFT JOIN table2
ON table1.fk = table2.pk
LEFT JOIN table3
ON table2.fk = table3.pk
WHERE other_conditions
...
You can repeat that as many levels as you want (performance depends on
indexing and the optimizer). You need to think in terms of what would
be equal to what between tables in the correct result row. So if you
would do a secondary sub-select of "SELECT fk from table2 where ..."
then you end up with a left join like above.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
