List:General Discussion« Previous MessageNext Message »
From:Chris Sansom Date:April 25 2006 3:44pm
Subject:Re: Stumped again by joins
View as plain text  
At 15:56 +0200 25/4/06, Barry wrote:
>And you don't see any misdone queries when you echo them, right?
>Hope you checked that.

Hi Barry

I was wrong about its being a PHP issue: it's 
definitely a MySQL error. I realised I hadn't 
handled the error in such a way that I could see 
what it was, but now I have, so...

The full query, in all its hideousness (but 
prettied up a bit in the formatting :-) ) is:

----------

select count(distinct uid) as c

from aptg_guides_restricted as r, aptg_guides as g

left join guides_biography as b on b.guide_id = r.uid
left join guides_interests as i on i.guide_id = r.uid
left join guides_tours as t on t.guide_id = r.uid
left join guides_walks as w on w.guide_id = r.uid
left join guides_lectures as l on l.guide_id = r.uid

where g.guide_uid = r.uid and show_on_web = '1' and
(b.biography like '%london%' or i.interests like 
'%london%' or t.tours like '%london%' or w.walks 
like '%london%' or l.lectures like '%london%')

----------

and the error I get back is:
Unknown column 'r.uid' in 'on clause'

...but I can assure you there is definitely a 
'uid' column in aptg_guides_restricted. If I take 
out the 'r.' from those left joins (there's no 
uid in any other table mentioned here) I get 
basically the same error: Unknown column 'uid' in 
'on clause'. And if I spell out 
'aptg_guides_restricted.uid' in the joins I 
/still/ get the error: Unknown column 
'aptg_guides_restricted.uid' in 'on clause'

So what /is/ the problem here? I say again: this 
and /exactly/ this worked perfectly in MySQL 
3.23, so there's obviously some change in syntax 
handling or whatever between versions.

In fact, this is a preliminary query to establish 
the total. If there is a total, I then run this:

----------

select distinct uid, firstname, lastname, 
year_qualified, other_qualifications, 
guide_driverguide, guide_photo_1

from aptg_guides_restricted as r, aptg_guides as g

left join guides_biography as b on b.guide_id = r.uid
left join guides_interests as i on i.guide_id = r.uid
left join guides_tours as t on t.guide_id = r.uid
left join guides_walks as w on w.guide_id = r.uid
left join guides_lectures as l on l.guide_id = r.uid

where g.guide_uid = r.uid and show_on_web = '1' and
(b.biography like '%london%' or i.interests like 
'%london%' or t.tours like '%london%' or w.walks 
like '%london%' or l.lectures like '%london%')

order by 
from_unixtime(unix_timestamp(guide_last_updated)) 
* (rand(1569933185) + ((length(guide_photo_1) > 
1) / 3)) desc

----------

...and if I run that directly in the SQL window 
in phpMyAdmin, I get the same error: Unknown 
column 'r.uid' in 'on clause'.

¿Qué?

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

I'm on a seafood diet - I see food, I eat it.
    -- Dolly Parton
Thread
Stumped again by joinsChris Sansom25 Apr
  • Re: Stumped again by joinsBarry25 Apr
    • Re: Stumped again by joinsChris Sansom25 Apr
    • Re: Stumped again by joinsChris Sansom25 Apr
      • Re: Stumped again by joinsgerald_clark25 Apr
        • Re: Stumped again by joinsChris Sansom25 Apr
      • Re: Stumped again by joinsPeter Brawley25 Apr
Re: Stumped again by joinsPhilippe Poelvoorde25 Apr