activerecord - SQL self referencing query -
i have schema 4 tables users, groups, memberships & membershipcontrols
memberships m:m between users & groups.
membership controls specify groups user must member of member of group.
for example member of "green" group user must member of either "blue" or "yellow" groups.
when user member of "green" group membership contingent upon them being member of either "blue" or "yellow" groups. if user ceases member of "blue" group existing memberships remain if cease member of "yellow" group "membership of "green" should deleted.
i trying work out sql delete records memberships in violation of membership controls.
http://sqlfiddle.com/#!9/302d2
based fiddle above following memberships valid: (1, 1, 1) (2, 1, 2) (3, 1, 3) (4, 1, 4)
if middle 2 memberships above of green & blue removed valid memberships be: (1, 1, 1)
i.e. if memberships table contained: (1, 1, 1) (1, 1, 4) last record invalid , should deleted in violation of membershipcontrols.
this because membership control specifies member of group: 4 need member of group: 2 or group: 3
try:
delete memberships user_id in ( select user_id memberships group user_id having sum(case when group_id = 4 1 else 0 end) = 1 , sum(case when group_id = 2 1 else 0 end) = 0 , sum(case when group_id = 3 1 else 0 end) = 0 ) , group_id = 4
to put simply, deletes rows memberships table user belongs group 4, not group 2 or 3 (and group 4 row).
Comments
Post a Comment