Hi all,

I've just moved my development server to FC5 which includes MySQL 5.
Everything is fine in my old apps except this one query!!

SELECT DISTINCT n.nav_order,
n.cat_id,
IF((LENGTH(c.category) > 0),c.category,s.title) as
title,
IF((LENGTH(c.category) > 0),'',n.page_id) as page_id
FROM navigation n,
story s
LEFT JOIN categories c ON c.cat_id = n.cat_id
WHERE nav_order IS NOT NULL AND
n.page_id = s.page_uid
ORDER BY nav_order

It used to work just fine but now it get the following error:
Couldn't execute navigation query.Unknown column 'n.cat_id' in 'on
clause'

Any ideas what's wrong?

TIA

Huw

huwpioden@gmail.com wrote:
> Hi all,
>
> I've just moved my development server to FC5 which includes MySQL 5.
> Everything is fine in my old apps except this one query!!
>
> SELECT DISTINCT n.nav_order,
> n.cat_id,
> IF((LENGTH(c.category) > 0),c.category,s.title) as
> title,
> IF((LENGTH(c.category) > 0),'',n.page_id) as page_id
> FROM navigation n,
> story s
> LEFT JOIN categories c ON c.cat_id = n.cat_id
> WHERE nav_order IS NOT NULL AND
> n.page_id = s.page_uid
> ORDER BY nav_order
>
> It used to work just fine but now it get the following error:
> Couldn't execute navigation query.Unknown column 'n.cat_id' in 'on
> clause'
>
> Any ideas what's wrong?
>
> TIA
>
> Huw

Try bracketing the FROM tables thus
FROM (navigation n,
story s)
LEFT JOIN

On Tue, 30 May 2006 19:14:07 +0100, "Paul Lautman"
<paul.lautman@btinternet.com> wrote:

>huwpioden@gmail.com wrote:

(...)
>> FROM navigation n,
>> story s
(...)

>Try bracketing the FROM tables thus
>FROM (navigation n,
> story s)
> LEFT JOIN

....or say

FROM navigation AS n, story
--
Pawe³
twierdza konserwy polskiej fizyki

huwpioden@gmail.com wrote:
> FROM navigation n,
> story s
> LEFT JOIN categories c ON c.cat_id = n.cat_id

MySQL 5.0.12 changed the precedence of comma-style joins vs. JOIN
syntax. Read more about that under the heading "Join Processing Changes
in MySQL 5.0.12" on this page:
http://dev.mysql.com/doc/refman/5.0/en/join.html

Anyway, it's better to use JOIN syntax throughout, for better
readability and predictable results:

FROM navigation n,
JOIN story s ON n.page_id = s.page_uid
LEFT JOIN categories c ON c.cat_id = n.cat_id

Regards,
Bill K.

On Tue, 30 May 2006 20:27:32 +0200, PFG
<gora@notthispart.if.uj.edu.pl> wrote:

>On Tue, 30 May 2006 19:14:07 +0100, "Paul Lautman"
><paul.lautman@btinternet.com> wrote:
>
>>huwpioden@gmail.com wrote:
>
>(...)
>>> FROM navigation n,
>>> story s
>(...)
>
>>Try bracketing the FROM tables thus
>>FROM (navigation n,
>> story s)
>> LEFT JOIN
>
>...or say
>
>FROM navigation AS n, story

Oops, a fragmented post. What I intended to say was:

.... or say

FROM navigation AS n, story AS s
LEFT JOIN
etc

--
Pawel