CODEWITHSUNDEEP
mysqllistnested
bob dope
bob dope

Reputation: 479

mysql SELECT IN followed by comma separated field

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

Upvotes: 5

Views: 4388

Answers (3)

Mark Byers
Mark Byers

Reputation: 838216

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.

Upvotes: 6

davidethell
davidethell

Reputation: 12018

Can you change this DB structure? The contacts field really should be a related table rather than a column. Assuming a contacts table with this structure:

id_contact
id_member

Then you would use EXISTS instead:

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

Upvotes: 2

tadman
tadman

Reputation: 211580

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.

Upvotes: 3

Related Questions