Queries within queries using MySQL

Question

I'm trying to order my search results more accurately. I specify a search string - e.g. "Beijing Olympics"

If the title column contains "Beijing Olympics" then the score 100 is added to the score, otherwise nothing is added
If the shortDescription column contains "Beijing Olympics" then 50 is added to the score, otherwise nothing is added
If the longDescription column contains "Beijing Olympics" then 10 is added to the score, otherwise nothing is added

In the end the maximum score possible per record would be 160 and I want the results to be ordered by highest score first and limited to a maximum of 10 results.

Below is definitely wrong, but it should illustrate what I am hoping to achieve!

SELECT 
    title, 
    id, 
    (
    IF((match 'Beijing Olympics' against title)*100) + 
    IF((match 'Beijing Olympics' against shortDescription)*50) + 
    IF((match 'Beijing Olympics' against longDescription)*10)
    ) as score 
from listings 
order by score desc limit 10

Matthew · Accepted Answer · 2011-05-06 16:29:30Z

1

You probably want to use CASE
http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

Otherwise I think your selection logic is fine.

SELECT 
    title
    ,id
    ,(
        (CASE WHEN title LIKE '%Beijing Olympics%' THEN 100 ELSE 0 END) 
        + (CASE WHEN shortDescription LIKE '%Beijing Olympics%' THEN 50 ELSE 0 END) 
        + (CASE WHEN longDescription LIKE '%Beijing Olympics%' THEN 10 ELSE 0 END) 
     ) AS score 
FROM 
    listings 
ORDER BY 
    score desc 
LIMIT 10

Note that I haven't tested this query, but it should be close.

EDIT: Also note that this matches for exact value.

edited May 6, 2011 at 16:29

answered May 6, 2011 at 16:04

Matthew

10.5k5 gold badges53 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Chris Over a year ago

Thank you for the reply. CASE is a new one to me! I'm getting a MySQL error even if I try a cut down version: SELECT title ,id, (CASE title WHEN 'Beijing Olympics' THEN 100 ELSE 0) as score FROM listings #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as score FROM listings

Chris Over a year ago

Ah ha, looks like I need to add END

Chris Over a year ago

Strange - if I use END CASE at the end of each line I get an error, if I just use END then the query completes successfully. How do I tweak your beautiful example so it checks to see if the columns contain (i.e. LIKE) "Beijing Olympics" rather than being equal to "Beijing Olympics". I appreciate your help!

Matthew Over a year ago

Just replace the equality with the LIKE I've edited the answer again :D

Chris Over a year ago

You genius! It works - I just needed to add a LIKE after longDescription on the third line

Collectives™ on Stack Overflow

Queries within queries using MySQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related