Welcome to WebmasterWorld Guest from 107.21.159.218

Forum Moderators: open

Message Too Old, No Replies

MySQL: How to do a MATCH of a set of SELECT results?

     

NeedExpertHelp

7:46 pm on Jun 2, 2010 (gmt 0)

5+ Year Member



Hi,

How would I get the results from QUERY1 that best matched the text in QUERY2:

QUERY1:


SELECT *
FROM table1, table2
WHERE
table1.col1 = table2.col1 AND
table1.col2 > 21 AND
table1.col2 < 31 AND
(table1.col3 = 'texta' OR table1.col3 = 'textb' OR table1.col3 = 'textc' OR table1.col3 = 'textd' OR table1.col3 = 'texte' ) AND
table2.col2 = 'textf' AND
table1.col4 = 'textg' AND
table1.col5 = 'texti' AND
table1.col6 = 'textj'


-------------------------

QUERY2:


SELECT * ,
MATCH (
table2.col3
)
AGAINST (
'some text'
) AS score
FROM table2
WHERE MATCH (
table2.col3
)
AGAINST (
'some text'
)
ORDER BY score DESC


-------------------------

In other words, I want to combine both queries such that QUERY1 is processed first, and then sorted based on the "score" value of QUERY2. Individually, each query works as it should, I just don't know how to combine them.

How would I do that?

Thanks!

whoisgregg

9:40 pm on Jun 3, 2010 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Either of these should work. I bet the second will be the faster than the first, but I would recommend testing each to make sure they do what you want and to see which is faster. :)

SELECT * , 
MATCH (
a.col3
)
AGAINST (
'some text'
) AS score
FROM (
SELECT *
FROM table1, table2
WHERE
table1.col1 = table2.col1 AND
table1.col2 > 21 AND
table1.col2 < 31 AND
(table1.col3 = 'texta' OR table1.col3 = 'textb' OR table1.col3 = 'textc' OR table1.col3 = 'textd' OR table1.col3 = 'texte' ) AND
table2.col2 = 'textf' AND
table1.col4 = 'textg' AND
table1.col5 = 'texti' AND
table1.col6 = 'textj'
) AS a
WHERE MATCH (
a.col3
)
AGAINST (
'some text'
)
ORDER BY score DESC

OR
SELECT *, MATCH ( 
table2.col3
)
AGAINST (
'some text'
) AS score
FROM table1, table2
WHERE
table1.col1 = table2.col1 AND
table1.col2 > 21 AND
table1.col2 < 31 AND
(table1.col3 = 'texta' OR table1.col3 = 'textb' OR table1.col3 = 'textc' OR table1.col3 = 'textd' OR table1.col3 = 'texte' ) AND
table2.col2 = 'textf' AND
table1.col4 = 'textg' AND
table1.col5 = 'texti' AND
table1.col6 = 'textj' AND
MATCH (
table2.col3
)
AGAINST (
'some text'
)
ORDER BY score DESC
 

Featured Threads

Hot Threads This Week

Hot Threads This Month