many thanks Chris :)
g
On May 24, 2006, at 1:19 AM, 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 ?
>>
>> Many thanks
>>
>>
>> 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
>
> From my limited knowledge (I'm a relative newbie and open to
> correction!) one syntax would be:
>
> SELECT category.name, page.name, content.title, content.body
> FROM category INNER JOIN page INNER JOIN 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
>
> ...ie., simply replace your commas with 'INNER JOIN'. Or you could
> do this:
>
> SELECT category.name, page.name, content.title, content.body
> FROM category
> INNER JOIN page ON page.category_id = category.id
> INNER JOIN content ON content.page_id = page.id
> WHERE category.id = 1
> ORDER BY content.order_id ASC
> LIMIT 0 , 30
>
> As for advantages, I'm not sure there are any for this particular
> query. The advantages would arise if you were to combine it with
> different JOINs, eg LEFT JOIN, because (in MySQL 5.x anyway) 'INNER
> JOIN' has a higher syntactical priority than the comma, which is
> the lowest priority of all. In other words, if you were to put a
> LEFT JOIN after your comma joins, MySQL would try to execute the
> LEFT JOIN first, but if you used INNER JOIN, that would be done first.
>
> I think that's about right. :-)
>
> --
> Cheers... Chris
> Highway 57 Web Development -- http://highway57.co.uk/
>
> I wonder who discovered you could get milk from a cow...
> and what on _earth_ did he think he was doing?
> -- Billy Connolly
