1

I'm trying to concatenate multiple id's brought in as a long string. Here is my query below. The problem I am getting is I only get the first id from my result. Now if I take the quotes out it will work perfectly but it is not brought in that way. It is brought in surrounded in quotes '64,66,63' I want to get all three id's '64,66,63' and I've done some digging on the CONCAT function for bringing this together but I can't get it to work. Any help is appreciated. If I'm going about this all wrong, I'm all ears.

select 
user.id,user.firstname,user.lastname,user.type,user.email,group_user.deleted,
group_user.user_id,group_user.title,group_user.group_id
from user right join group_user on user.id = group_user.user_id 
where group_user.title != 'Special' and group_user.group_id in ('64,66,63') 
and group_user.deleted = 0 and user_id != ''
Shapi
  • 5,493
  • 4
  • 28
  • 39
wowzuzz
  • 1,398
  • 11
  • 31
  • 51
  • The problem is that IDs should not be stored as a comma-delimited string. You should have a subtable that has group IDs in them. See this question for a discussion: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Andy Lester Oct 27 '15 at 20:49
  • What do you mean by "brought in"? If you want to pull rows with group id 64, 66, and 63 just remove the quotes from the id list in your where clause. – Dan Oct 27 '15 at 20:50
  • I implode it before I put it into the query. I take it from an array and separate them with a comma delimiter. – wowzuzz Oct 27 '15 at 20:52

1 Answers1

1

One way is to use FIND_IN_SET:

WHERE ...
  AND FIND_IN_SET(group_user.group_id, '64,66,63') > 0

Keep in mind that this is not the most efficient way.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • This worked perfectly for me. I will work on getting it to implode right but this works for now. Thank you! – wowzuzz Oct 27 '15 at 21:03