List:General Discussion« Previous MessageNext Message »
From:Chris Sansom Date:April 25 2006 1:23pm
Subject:Stumped again by joins
View as plain text  
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 <select>s 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
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