List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 25 2006 8:34pm
Subject:Re: Stumped again by joins
View as plain text  
Chris,

 >select count(distinct uid) as c
 >from aptg_guides_restricted as r, aptg_guides as g
...<snip>...

See the extensive notes on comma and SQL2003 joins at 
http://dev.mysql.com/doc/refman/5.1/en/join.html.
Lose the comma join, make it a SQL2003 (explicit inner) join.

PB

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


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 4/24/2006

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