Mysql SELECT IN followed by a comma separated field

Question

I use mySQL, and I have a member table with a BLOB "contacts" field containing a list of other member identifiers, separated by commas:

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

I want to get all the first names in the contact list of an individual user with one request. 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 check twice if there is a way to make it work with one simple, elegant query.

Thanks for reading, any help appreciated. July

Answer 1

Can you change this database structure? The contact field should really be a linked table, not a column. Assuming a contact table with this structure:

 id_contact id_member 

Then you should use EXISTS instead:

 SELECT firstname from members m WHERE EXISTS (SELECT 1 FROM contacts c WHERE c.id_contact = m.id_member ); 

Answer 2

It looks like a very bad desk design. Is it possible to change it?

If you cannot change the design, you can handle comma-separated values ​​in MySQL using FIND_IN_SET , but this will not 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 instead of taking this route, I strongly recommend that you normalize your database if possible. Consider using a connection table instead of a comma separated list. Then you can find the records you need using joins, and the search will be able to use the index.

Answer 3

If you use a serialized BLOB column to store these values, you cannot do what you want. Another approach to SQL is to create a relationship table that can be used as part of a JOIN operation, such as the member_contacts table, which has a relationship between one id_member value and some other.

Expanding a comma-separated list into separate entries is a fairly simple mechanical process.