homepage Welcome to WebmasterWorld Guest from 54.242.140.11
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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

5+ Year Member



 
Msg#: 4145852 posted 7:46 pm on Jun 2, 2010 (gmt 0)

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

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



 
Msg#: 4145852 posted 9:40 pm on Jun 3, 2010 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved