CODEWITHSUNDEEP
mysqlsqlfull-text-search
Jimit
Jimit

Reputation: 2259

Fulltext search query mysql

I have ingredients table in my db. I want to fetch those recipe who contains some ingredients and not contain some ingredients.

For example, I have 2 recipes which both contains "pav" but one of them not contains "phudina". Now, below is my db table data.

Here in table(ingredients) recipeID 24(rcteID) contains pav and phudina
-----------------------------------------------------------------------
 ingredientID   rcteID  ingredient
    319          24     phudina
    320          24     pav     

Here in table(ingredients) recipeID 23(rcteID) not contains phudina
-----------------------------------------------------------------------
 ingredientID   rcteID  ingredient
    316          23     test    
    317          23     pav     
    318          23     puri

Now I want those recipe ids which contains "pav" but not the "phudina" so, here it is 23 but my query is giving me both recipes. I have applied Fulltext index on ingredient column. 

Below is the query which I have written.

SELECT  `Ingredient`.`ingredientID`, `Ingredient`.`rcteID` 
FROM `bdrplus`.`ingredient` AS `Ingredient`   
WHERE NOT(MATCH(`Ingredient`.`ingredient`) AGAINST('+phudina' IN BOOLEAN MODE)) 
AND MATCH(`Ingredient`.`ingredient`) AGAINST('+pav' IN BOOLEAN MODE)  
GROUP BY `Ingredient`.`rcteID

    Output
    ----------------------------
        ingredientID    rcteID
             317         23
             320         24
   Expected
   -----------------------------
        ingredientID    rcteID
             317         23

What is wrong with query?

Upvotes: 0

Views: 65

Answers (1)

Barmar
Barmar

Reputation: 780663

You're testing both conditions against each row, not looking at other rows with the same rcteID. That would work if you had all the ingredients in a single string, but it doesn't work when you normalize the table as you have. Use this:

SELECT ingredientID, rcteID
FROM ingredient
WHERE ingredient = 'pav'
AND rcteID NOT IN (
    SELECT rcteID
    FROM ingredient
    WHERE ingredient = 'phudina'
)

DEMO

There also doesn't seem to be a need to use full-text search when the ingredient column is just a single word. But you can replace the = condition with MATCH if you really do need it (maybe the actual data isn't like the examples).

Upvotes: 1

Related Questions