CODEWITHSUNDEEP
sqlcsvtags
Brai Kaler
Brai Kaler

Reputation: 31

SQL query find rows that share the same tags

Let's see if someone can help me... On a product page, I have to show related products. These products are related depending on their tags. For example my product (ID 23) has the following tags: cooking, noodles, healthy.

What I have to do, is a SQL query, that search for products that have the tags "cooking, noodles, healthy" but separately

First search for all the products that have "cooking" in their tags, then look for other products to have "noodles" , and finally "healthy".

The products shown have to be in order: first show all products that have "cooking" in the tags column, then "noodles" and lastly "healthy". Important, the products do not have to be repeated.

This is the order I hope to get: ID: 25, 25, 40, 43, 46

This is the database

ID Tags Name
23 cooking, noodles, healthy Noodles Hot white pepper
25 cooking, noodles, healthy Soap of noodles
35 cooking, noodles, healthy Noodles with carrots
40 food, noodles, ketchup New Noodles with ketchup
43 apple, cook, healthy Apple with sugar
46 banana, cook, healthy Banana with sugar

Thanks for the help!!

Upvotes: 0

Views: 65

Answers (1)

GMB
GMB

Reputation: 222572

The way you store your data makes things unnecessarily suboptimal. You should not have multiple strings stored in a single column; instead, there should be another table to store the relation between products and tags, with each tuple should on a separate row.

For your current design, you can do:

select t.*
from mytable t
where 
    ',' || tags || ',' like '%,cooking,%'
    or ',' || tags || ',' like '%,noodles,%'
    or ',' || tags || ',' like '%,healthy,%'
order by 
    case
        when ',' || tags || ',' like '%,cooking,%' then 1
        when ',' || tags || ',' like '%,noddles,%' then 2
        else 3
    end,
    id

This uses standard string concatenation operator ||: you might need to adapt that to your actual database.

Some databases have built-in functions to search a CSV list. If, for example, you are running MySQL:

select t.*
from mytable t
where 
    find_in_set('cooking', tags)
    or find_in_set('noodles', tags)
    or find_in_set('healthy', tags)
order by 
    case
        when find_in_set('cooking', tags) then 1
        when find_in_set('noodles', tags) then 2
        else 3
    end,
    id

Upvotes: 1

Related Questions