CODEWITHSUNDEEP
mysql
joshuahedlund
joshuahedlund

Reputation: 1782

Exact match, or do partial match if no exact match, in a single query?

I need to search a MySQL database under the following conditions:

1) If any exact match is found for the keyword, return the number of rows and the results with an exact match.

2) If no exact matches are found, return the number of rows and results with a partial match.

For example, if the keyword was cake, and the table had the following rows, the search should return only rows 1 and 5.

1| cake
2| pancake
3| cupcake
4| fruitcake
5| cake
6| pie

However, if only the following rows were present, the search should return only rows 2-4.

2| pancake
3| cupcake
4| fruitcake
6| pie

It is trivial to do this in two queries, such as:

SELECT SQL_CALC_FOUND_ROWS fields FROM table WHERE word = `cake`

//PHP checks for number of rows, if 0, do second query

SELECT SQL_CALC_FOUND_ROWS fields FROM table WHERE word LIKE '%cake%'

Question: To cut down on trips to the database, Is there any way to do this in a single query?

The best solution I can come up with is to match both via OR, using ORDER BY to favor the exact match. However, to cut off the partial matches if an exact match is found requires iterating through the results, which is infeasible if the exact match returns 2,000 results and the partial match returns 5,000 more but I only want to show the first 25 while still showing the 2,000 count.

Upvotes: 2

Views: 8032

Answers (4)

Gaurav Choudhary
Gaurav Choudhary

Reputation: 1

SELECT (CASE WHEN Exact.Apparence IS NULL THEN Partial.Apparence ELSE Exact.Apparence END) as Apparence FROM 
(SELECT COUNT(word) as Apparence FROM Table WHERE word='cake') as Exact INNER JOIN 
(SELECT COUNT(word) as Apparence FROM Table WHERE word like '%cake%') as Partial ON 1=1

Upvotes: 0

Andre
Andre

Reputation: 1044

EDIT 3:

The following does work:

SELECT 
    COALESCE(NULLIF(COUNT(exact.Id), 0), COUNT(partial.Id))
FROM
    Test AS partial
LEFT JOIN
    Test AS exact ON exact.Id = partial.Id AND exact.Name = 'pi'
WHERE
    partial.Name LIKE '%pi%'

I deleted my last text here because it was wrong.

EDIT 4:

The following is written in TSQL (I dont know the correct MySql syntax) and return the actual rows (you can wrap it in a StoredProcedure on your MySql server):

CREATE TABLE #Result (Id int, Name nvarchar(max));

INSERT INTO #Result
SELECT * FROM #YourCakeTable WHERE Name = 'cake'

if(FOUND_ROWS() = 0)
    INSERT INTO #Result
    SELECT * FROM #YourCakeTable WHERE Name like '%cake%'

SELECT * FROM #Result

Upvotes: 2

Hituptony
Hituptony

Reputation: 2860

SELECT word, count(*) as ExactMatches, 
if(count = 0, (SELECT count(*) from table WHERE word LIKE '%word%' order by word), NULL) as PartialMatches
FROM  table 
order by word

What I think would happen here is you would get your

word|count exacts|count partials|

cake|100| null
cake|0| 50

Upvotes: 0

ericpap
ericpap

Reputation: 2937

I'n not sure about MYSQL sintax but you can do this:

SELECT (CASE WHEN Exact.Apparence IS NULL THEN Partial.Apparence ELSE Exact.Apparence END) as Apparence FROM 
(SELECT COUNT(word) as Apparence FROM Table WHERE word='cake') as Exact INNER JOIN 
(SELECT COUNT(word) as Apparence FROM Table WHERE word like '%cake%') as Partial ON 1=1

Let me know if it helps you

Upvotes: 0

Related Questions