List:General Discussion« Previous MessageNext Message »
From:gerald_clark Date:April 25 2006 4:10pm
Subject:Re: Stumped again by joins
View as plain text  
Chris Sansom wrote:

> 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.

Yes. 3.23 was not correct in the order of precedence.
This has been answered many times here.
You need to change your comma join to an inner join.

select count(distinct uid) as c
from aptg_guides_restricted as r
inner join aptg_guides as g on g.guide_uid = r.uid
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 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%')



>
> 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é?
>

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