mysql SELECT IN followed by comma separated field

Question

I use mySQL and I have a members table with a BLOB 'contacts' field containing a comma separated list of other member's IDs:

TABLE members:
id_member = 1
firstname = 'John'
contacts (BLOB) = '4,6,7,2,5'

I want to retrieve all the first names in the 'contacts' list of an individual, with a single query. I tried the following:

SELECT firstname from members WHERE id_member IN ( SELECT contacts FROM members WHERE id_member = 1 );

It returns only one row, but when I try:

SELECT firstname from members WHERE id_member IN ( 4,6,7,2,5 );

It returns all the first names from the list. I can use two queries to achieve this, but I thought I'd double check if there's a way to make it work with one simple, elegant query.

Thanks for reading, any help appreciated. Jul

Answer 1

That seems like a very poor table design. Is it possible to change it?

If you can't change the design then you can handle comma separated values in MySQL by using FIND_IN_SET but it won't be able to use indexes efficiently:

SELECT firstname
FROM members
WHERE FIND_IN_SET(id_member, (SELECT contacts FROM members WHERE id_member = 1))

But rather than going this route, I'd strongly recommend that if possible you normalize your database. Consider using a join table instead of a comma separated list. Then you can find the entries you need by using joins and the search will be able to use an index.

Answer 2

If you're using a serialized BLOB type column to store these values then you're not going to be able to do what you want. A more SQL friendly approach is to create a relationship table that can be used as part of a JOIN operation, such as a member_contacts table that has an association between one id_member value and some other.

Expanding your comma separated list into individual records is a pretty simple mechanical process.