MySQL Forums
Forum List  »  Newbie

Re: Need Help W/ Query
Posted by: Phillip Ward
Date: May 22, 2012 06:08AM

Quote

I have a table with a comma-separated 'groups_members' value (eg 42,43,47,51) ...
And now you've discovered what a Bad Idea that is.

Split the values out into a separate table with the key of the parent record plus the individual values, so instead of :
   Groups 
   +----------+------------+-------------+---- 
   | group_id | group_name | members     | ... 
   +----------+------------+-------------+---- 
   |        1 | group one  | 42,43,47,51 | ... 
   +----------+------------+-------------+----

you should have something more like:
Groups 
+----------+------------+---- 
| group_id | group_name | ... 
+----------+------------+---- 
|        1 | group one  | ... 
+----------+------------+---- 

Group Members 
+----------+-----------+ 
| group_id | member_id | 
+----------+-----------+ 
|        1 |        42 | 
|        1 |        43 | 
|        1 |        47 | 
|        1 |        51 | 
+----------+-----------+

Now, to remove a member from a group, you issue a delete statement.
   delete from Group_Members 
   where group_id = 1 
   and member_id = 43 ;

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
May 22, 2012 03:15AM
May 22, 2012 03:47AM
May 22, 2012 07:36AM
Re: Need Help W/ Query
May 22, 2012 06:08AM
May 23, 2012 02:37PM
May 23, 2012 03:43PM
May 23, 2012 05:35PM
May 26, 2012 11:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.