Yes it's legal to do multiple join. No, the way you declared you joins is not legal. At the very least it confusing. Do you want to
left join discussion, users and topics, or just memebers?
I'm not sure if MySQL would accept things in that order. I always specifically declare my joins (left or otherwise), it makes things
more readable. You'll dicover that you are missing a couple of join specifications by structuring it differently.
select m.*, d.discussion, d.discussion_id, u.user_id, t.topic_id
FROM forums_messages, forums_discussions d, users u,
LEFT JOIN forums_topics t on t.discussion_id = d.discussion_id
LEFT JOIN forums_members m on m.topic_id = t.topic_id
WHERE m.topic_id = "1";
You are not stating how you are going to join discussions and users to the messages table. Thus it's going to do a full join, which
you never want. By always specifically declaring all your joins, you end up with something like this:
select m.*, d.discussion
JOIN d.discussion_id ON ?
JOIN u.user_id ON ?
JOIN t.topic_id ON ?
FROM forums_messages, forums_discussions d, users u,
LEFT JOIN forums_topics t on t.discussion_id = d.discussion_id
LEFT JOIN forums_members m on m.topic_id = t.topic_id
WHERE m.topic_id = "1";
The ? are missing join specifications. This is actually the desired query format going forward (i.e. MySQL 5), so you may want to
start using it.
----- Original Message -----
From: "Steffan A. Cline" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Friday, June 30, 2006 10:30 AM
Subject: Multiple joins
What am I missing here?
select m.*, d.discussion, d.discussion_id, u.user_id, t.topic_id
from forums_messages
left join forums_members m, forums_discussions d, users u, forums_topics t
on m.topic_id = t.topic_id, t.discussion_id = d.discussion_id
where m.topic_id = "1";
Is it legal to do multiple joins like this?
Thanks
Steffan
---------------------------------------------------------------
T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net USA
AIM : SteffanC ICQ : 57234309
Lasso Partner Alliance Member
---------------------------------------------------------------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
