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. :-)
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
Justice is incidental to law and order.
-- J. Edgar Hoover
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
