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" <steffan@stripped>
To: <mysql@stripped>
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
> Steffan@stripped 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/mysql?unsub=1
>
