From: Peter Brawley
Date: May 24 2006 7:32pm
Subject: Re: Noob: Converting to Inner Join
List-Archive: http://lists.mysql.com/mysql/198269
Message-Id: <4474B4EA.6030908@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >Surely the column name has to exist in both tables?
Indeed.
 >Graham is using page.category_id and category.id, content.page_id and 
page.id
His column specs weren't complete. If you're right on this, though...
 >, so I think ON (as I posted earlier) is the only way to do this.
...you're right on that too.

PB


Chris Sansom wrote:
> At 23:17 -0700 23/5/06, Graham Anderson wrote:
>> Are there any advantages to converting this 'working' query below to
>> use INNER JOIN ?
>> If so, what would the correct syntax be ?
>>
>> SELECT category.name, page.name, content.title, content.body
>> FROM category, page, content
>> WHERE content.page_id = page.id
>> AND page.category_id = category.id
>> AND category.id =1
>> ORDER BY content.order_id ASC
>> LIMIT 0 , 30
>
> And at 11:52 -0500 24/5/06, Peter Brawley wrote:
>> Explicit INNER JOINs are easier to read, easier to debug, and since 
>> 5.0.12 always preferable in MySQL for reasons given at 
>> http://dev.mysql.com/doc/refman/5.1/en/join.html (look for '5.0.12').
>>
>> SELECT category.name, page.name, content.title, content.body
>> FROM category
>> INNER JOIN content USING (category_id)
>> INNER JOIN page USING (page_id)
>> WHERE category.id = 1
>> ORDER BY content.order_id ASC
>> LIMIT 0 , 30
>
> Actually, although I've never used the USING clause - I just looked it 
> up - I don't think this would work. Surely the column name has to 
> exist in both tables? Graham is using page.category_id and 
> category.id, content.page_id and page.id, so I think ON (as I posted 
> earlier) is the only way to do this.
>
> Willing to be corrected though. :-)
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: 5/24/2006