CODEWITHSUNDEEP
sqlsqlite
Favolas
Favolas

Reputation: 7243

Understanding SQLite query

I'm training with SQLite and i'm trying to achieve the same result as the search result implemented in USDA.

In USDA food list search if I search for "chicken breast cooked roasted" the search results 56 results. I'm can't figure out the pattern. Using the same database as they, If i use the following query:

SELECT *
  FROM FOODDATA
 WHERE FOODDES LIKE '%chicken%' OR FOODDES LIKE '%breast%' OR FOODDES LIKE '%cooked%' OR FOODDES LIKE '%roasted%';

It gives me more than 2000 results. For me that makes sense. Using AND replacing the OR only gives me 2 results. Once again, it makes sense.

What kind of query are USDA performing?

Upvotes: 0

Views: 85

Answers (3)

sgmoore
sgmoore

Reputation: 16067

Try something like

 select 
 (FOODDES LIKE '%chicken%') + (FOODDES LIKE '%breast%') + (FOODDES LIKE '%cooked%') +  (FOODDES LIKE '%roasted%') As matches ,  
 FOODDES
 from 
 (
     SELECT FOODDES
     FROM FOODDATA
     WHERE FOODDES LIKE '%chicken%' or FOODDES LIKE '%breast%' or FOODDES LIKE '%cooked%' or FOODDES LIKE '%roasted%'
  ) table1
  where matches >=3
  order by matches desc

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can investigate the number of matches to the keywords using this query:

SELECT ((FOODDES LIKE '%chicken%') +
        (FOODDES LIKE '%breast%') +
        (FOODDES LIKE '%cooked%')
        (FOODDES LIKE '%roasted%')
       ) as NumMatches, count(*), min(fooddes), max(fooddes)
FROM FOODDATA
GROUP BY (FOODDES LIKE '%chicken%') +
         (FOODDES LIKE '%breast%') +
         (FOODDES LIKE '%cooked%')
         (FOODDES LIKE '%roasted%')
ORDER BY NumMatches desc;

This query just counts the number of keywords that match and give the number of rows in FOODDATA that have 4 matches, 3 matches, and so on.

Upvotes: 1

Googie
Googie

Reputation: 6017

Use AND instead of OR:

SELECT *
  FROM FOODDATA
 WHERE FOODDES LIKE '%chicken%' AND FOODDES LIKE '%breast%' AND FOODDES LIKE '%cooked%' AND FOODDES LIKE '%roasted%';

Also the character case may cause the difference. You are looking only for lowercase words and notice, that lots of their results start with a Chicken, with an uppercase. Add uppercase cases to your AND conditions and see results.

Also the Full Text Search extension to SQLite is a very good fit for this kind of queries (that is queries where you look for some words in longer texts). The LIKE is very slow (as for how fast databases are).

Upvotes: -1

Related Questions