List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:June 30 2006 3:06pm
Subject:Re: Multiple joins
View as plain text  
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
> 

Thread
Multiple joinsSteffan A. Cline30 Jun
  • Re: Multiple joinsGerald L. Clark30 Jun
    • Re: Multiple joinsSteffan A. Cline30 Jun
  • Re: Multiple joinsBrent Baisley30 Jun