select a row using a value in comma separated fields

Question

I have to select a row from a table 'seller' which have two columns 'cat' and 'place' in which multiple ids are stored with commas

'seller' table
╔═══╦════════╦══════════════╗
║ id║cat     ║    place     ║
╠═══╬════════╬══════════════╣ 
║ 8 ║1,2     ║ 1,2,3,4      ║
╚═══╩════════╩══════════════╝

my mysql query

SELECT * FROM `seller` WHERE cat like '%1%' && place LIKE '%2%'

but i know it is bad query.if there is 12 it will treated as both 1 and 2. is there any good query??.

i have to store multiple ids in fields thats why i used comma separation — Siby Xavier, Dec 28 '16 at 07:01
@Suchit: https://stackoverflow.com/questions/11957643/mysql-variable-format-for-a-not-in-list-of-values/11957706#11957706 — juergen d, Dec 28 '16 at 07:04

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

Use FIND_IN_SET

SELECT * FROM `seller` 
WHERE find_in_set(1, cat) > 0
and find_in_set(2, place) > 0

But you should actually change your table design.

Never store multiple values in a single column!

That is a classic n to m relation. A better design would be

seller table
------------
id
name
...


categories table
----------------
id
name


seller_categories table
-----------------------
seller_id
category_id

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 28 '16 at 07:01

juergen d

201,996
37
293
362

Thanks.your query working fine.I am using a multiselect dropdown to store in both columns thats why i used comma. – Siby Xavier Dec 28 '16 at 07:07
There is no excuse for using a comma separated list in a column. None! – juergen d Dec 28 '16 at 07:07

select a row using a value in comma separated fields

1 Answers1

Never store multiple values in a single column!