Search comma separated values

Databases
1

Hi,

I have a column “services” in the table

The data in the column is saved as follows
5
1 ,3, 22, 13,
7, 13, 1
etc…

How can I search this field in mysql? for example, I would like to return all the rows who have the service 13. Is there a ready-to-use function in Mysql than can do this?

Thanks.

2

yeah, except no “id” column, just the two foreign keys (as a composite primary key)

3

Thank you…

Do you think that this query would take so much time to load? There aren’t too many services (around 15) and the number of vendors would not exceed 1000…
So what you suggest is having a table which acts as an intermediate between vendors and services

id vendor_id service_id

4 
SELECT v.name
     , v.city
  FROM WHERE 
  FROM tbl_services AS s
INNER
  JOIN tbl_vendors AS v
    ON FIND_IN_SET(s.id,v.services) > 0 
 WHERE s.parent_id = 5

seriously, i urge you to redesign your table

5

Hello,

I would like to do the following…

I have a table called services …First, I would like to return all the services ids that have a parent_id=5 …

This would return the following:
id
1
3
7
11

Then I would like to return all the vendors (tbl_vendors) who have the services returned from the previous query…

However, services are saved in table vendors as comma separated values
tbl_vendors
vendor_id services
2 ****** 1, 7, 11
3 ****** 3,1,12

Hope you got me well this time?

Thanks

6

sorry, i don’t understand what you’re trying to do

7

yes, it’s called FIND_IN_SET

please note, however, that your queries will not scale – i.e. the more rows you have, the slower the query will be

the only way to fix it is to normalize your data

storing a comma-delimited string of id values in a single column is usually but not always a bad design decision – it’s not bad provided that you never need to search within the string

8

Thanks a lot Rudy …

I have one more question … I think a more difficult one …

SELECT * FROM tbl_vendors WHERE FIND_IN_SET(SELECT id
FROM tbl_services
WHERE parent_id = ‘5’, services)

SELECT id
FROM tbl_services
WHERE parent_id = ‘5’ return more than a single row

What can be done in such cases? I want to do find in set for all the returned rows from tbl_services…

Thanks