Chris Sansom wrote:
> At 11:01 -0400 10/9/07, Baron Schwartz wrote:
>> The entire UNION can then be ordered by relevance. You could also
>> just add in an arbitrary number in each UNION, to get the effect of
>> ordering by where in the hierarchy the match is found.
>
> Actually, your pointing me towards UNION may have done the trick. I read
> up on it on the MySQL docs site and I've ended up with this, which
> actually covers more tables and fields than in my original post:
>
> -----------
>
> select distinct tb.speaker_id, tb.fore, tb.sur, tb.division from
> (
> (
> select 1 as relevance, speaker_id, fore, sur, division
> from speakers
> where fore like '%education%' or sur like '%education%')
> union
> (
> select 2 as relevance, s.speaker_id, fore, sur, division
> from speakers s, speakers_topics st, topics t
> where st.speaker_id = s.speaker_id and t.topic_id = st.topic_id and
> topic like '%education%'
> )
> union
> (
> select 3 as relevance, speaker_id, fore, sur, division
> from speakers where match (strap, shortbio, longbio) against ('education')
> )
> union
> (
> select 4 as relevance, s.speaker_id, fore, sur, division
> from speakers s, articles a
> where s.speaker_id = a.speaker_id and match (title, article) against
> ('education')
> )
> union
> (
> select 5 as relevance, s.speaker_id, fore, sur, division
> from speakers s, other o
> where s.speaker_id = o.speaker_id and match (title, article) against
> ('education')
> )
> union
> (
> select 6 as relevance, speaker_id, fore, sur, division
> from speakers, books
> where speaker_id = author and match (title, description) against
> ('education')
> )
> order by relevance, division, sur, fore
> ) as tb
>
> -----------
>
> First, I did it without the outer select, and I got speakers repeated if
> they were matched in more than one block. One of the comments on the
> MySQL docs site suggested the 'wrapper', which I did initially like this:
>
> select distinct speaker_id, fore, sur, division from... with nothing
> after the final ')'. This gave me an error to the effect that derived
> tables must always have an alias. What the hey, let's just try it like
> this (the above)... and to my astonishment it worked!
>
> So before I sign off on this thread, can you see any way I could improve
> this?
>
> Naturally, I haven't yet incorporated the treatment of more than one
> search term, but I'll try and work that out for myself. :-)

Looks like you've found the solution you need. The only other
suggestion I have is to use UNION ALL if you don't need to eliminate
duplicate rows in the UNION, because there's some overhead for checking
for them.

Baron