Stumped again by joins
As a relative newbie, and an almost total newbie to the use of left joins, I'm aware that there's some difference in the way joins work between MySQL 3.x and 5.x, but in my ignorance I can't figure out what the heck it is from reading the 'upgrading' pages on dev.mysql.com. When I first joined this list (joined - geddit?), Barry in particular solved a search problem for me by introducing left joins. A simplified version of my query is this (it's a database of tourist guides, where I've entered 'olympic' into the catch-all text field at the bottom): select [fields I want to display] from guides as g left join biography as b on b.guide_id = g.id left join interests as i on i.guide_id = g.id left join tours as t on t.guide_id = g.id left join walks as w on w.guide_id = g.id left join lectures as l on l.guide_id = g.id where show_on_web = '1' and (b.biography like '%olympic%' or i.interests like '%olympic%' or t.tours like '%olympic%' or w.walks like '%olympic%' or l.lectures like '%olympic%') order by ... This worked like a charm (with fulltext indices on the text fields being searched in those five tables) in 3.23.x, but now it falls over and finds nobody at all in 5.0.19. The rest of the search is fine - there are various selects and checkboxes on which you can search and as long as I type nothing into the catch-all it behaves perfectly, but as soon as I do I get a zero result. (The whole bit with the left joins only gets added to the query if there's something in the catch-all.) The other major change is that I'm now using the utf8 charset throughout the database and scripts, whereas before, with 3.23 not supporting it, I was utf8_decode()ing everything that went to MySQL and utf8_encode()ing everything that came out of it. I did try putting back the utf8_decode() round the catch-all search string, but (as I expected) it made no difference. At first I thought the upgrade or utf8 might be having some effect on the way like '%...%' works, but another simpler search uses that and it's fine. The whole point of having five separate tables for those elements is that guides can record their information in a number of languages, so there's a row per guide per language in each table - or maybe none at all (not so many guides offer lectures, for example). I want users to be able to find text in any of the languages on offer. Where am I going wrong? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ The lead car is absolutely unique, except for the one behind it which is identical. -- Murray Walker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
Chris Sansom schrieb: As a relative newbie, and an almost total newbie to the use of left joins, I'm aware that there's some difference in the way joins work between MySQL 3.x and 5.x, but in my ignorance I can't figure out what the heck it is from reading the 'upgrading' pages on dev.mysql.com. Updating is always such a bad idea ;P Do you know: never touch a running system? ^_^ This worked like a charm (with fulltext indices on the text fields being searched in those five tables) in 3.23.x, but now it falls over and finds nobody at all in 5.0.19. The rest of the search is fine - there are various selects and checkboxes on which you can search and as long as I type nothing into the catch-all it behaves perfectly, but as soon as I do I get a zero result. (The whole bit with the left joins only gets added to the query if there's something in the catch-all.) And you don't see any misdone queries when you echo them, right? Hope you checked that. The other major change is that I'm now using the utf8 charset throughout the database and scripts, whereas before, with 3.23 not supporting it, I was utf8_decode()ing everything that went to MySQL and utf8_encode()ing everything that came out of it. I did try putting back the utf8_decode() round the catch-all search string, but (as I expected) it made no difference. ENCODE = NOT CODED into CODED DECODE = CODED into NOT CODED So encode the input into query and encode it afterwards :) At first I thought the upgrade or utf8 might be having some effect on the way like '%...%' works, but another simpler search uses that and it's fine. So in simple words. You tried also to query the Table without encoding it first into UTF-8? The whole point of having five separate tables for those elements is that guides can record their information in a number of languages, so there's a row per guide per language in each table - or maybe none at all (not so many guides offer lectures, for example). I want users to be able to find text in any of the languages on offer. Where am I going wrong? There are various, and the main spot here is the ENCODING of UTF-8. More infos will be great. It surely is tricky. select [fields I want to display] from guides as g left join biography as b on b.guide_id = g.id left join interests as i on i.guide_id = g.id left join tours as t on t.guide_id = g.id left join walks as w on w.guide_id = g.id left join lectures as l on l.guide_id = g.id where show_on_web = '1' and (b.biography like '%olympic%' or i.interests like '%olympic%' or t.tours like '%olympic%' or w.walks like '%olympic%' or l.lectures like '%olympic%') order by ... I am not quite sure but using biography.guide_id instead of b.guide_id would probably solve the problem. I know that using aliases in WHERE clauses don't work really good, so try this also please. Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
At 15:56 +0200 25/4/06, Barry wrote: Updating is always such a bad idea ;P Do you know: never touch a running system? ^_^ Hmmm... And you don't see any misdone queries when you echo them, right? Hope you checked that. Yes, they look just fine - in any case they're unchanged from when it was working perfectly in 3.23 ENCODE = NOT CODED into CODED DECODE = CODED into NOT CODED So encode the input into query and encode it afterwards :) Yes, tried that - no good. In any case... At first I thought the upgrade or utf8 might be having some effect on the way like '%...%' works, but another simpler search uses that and it's fine. ...there's no en/decoding involve there and it works fine. So in simple words. You tried also to query the Table without encoding it first into UTF-8? With and without. But anyway, as I understand it, something like 'olympic' or 'london' (another thing I'm test-searching for because just about every guide mentions London somewhere in their biography - they're London guides, see? :-) ) is the same whether or not it's encoded... no? There are various, and the main spot here is the ENCODING of UTF-8. That's more or less the conclusion I've come to as well, but I can't seem to make a difference whichever way round I do it. I'm also wondering now if it might be a PHP issue after all - something I've missed about form input, but I realise this list isn't the right place to follow that up. More infos will be great. It surely is tricky. Yep. I am not quite sure but using biography.guide_id instead of b.guide_id would probably solve the problem. I know that using aliases in WHERE clauses don't work really good, so try this also please. OK, I have, and it still makes no difference. Anyway, I reiterate: it worked perfectly in 3.23.x - surely something basic like this couldn't have got /worse/ through the version upgrades? And for what it's worth I tried taking the 'as' out too, which I gather is now optional - no diff. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Star Wars won't work. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
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é? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
2006/4/25, Chris Sansom [EMAIL PROTECTED]: 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' in 5.0.12 comma precedence was changed : http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html so try with parenthesis, your implicit join and left join should then works correctly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
At 11:10 -0500 25/4/06, gerald_clark wrote: Yes. 3.23 was not correct in the order of precedence. This has been answered many times here. Sorry - I haven't been on the list all that long. You need to change your comma join to an inner join. Lovely! That's it - many thanks. At 17:15 +0100 25/4/06, Philippe Poelvoorde wrote: in 5.0.12 comma precedence was changed : http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html I did look at this but, my understanding of joins still being somewhat shaky, I didn't take in its full implications. so try with parenthesis, your implicit join and left join should then works correctly. In fact it's easier in my script to use inner join as Gerald suggested. Many thanks for your patience though. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I think I think; therefore I think I am. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
