List:General Discussion« Previous MessageNext Message »
From:Chris Sansom Date:May 24 2006 5:01pm
Subject:Re: Noob: Converting to Inner Join
View as plain text  
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. :-)

-- 
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

It was a woman who drove me to alcohol, I must write and thank her
    -- W.C. Fields
Thread
Noob: Converting to Inner JoinGraham Anderson24 May
  • Re: Noob: Converting to Inner JoinChris Sansom24 May
    • Re: Noob: Converting to Inner JoinGraham Anderson24 May
    • Re: Noob: Converting to Inner JoinPeter Brawley24 May
      • Re: Noob: Converting to Inner JoinChris Sansom24 May
        • Re: Noob: Converting to Inner JoinPeter Brawley24 May