All Forums Database
pavan.polish 18 posts Joined 09/12
28 Jul 2013
Selecting groups whose member belongs to multiple groups

Hi All,
I have a below requirement:
I have data like below with Group ID and Member ID. I want to extract both Group ID and Member ID details for which a member belongs to more than one group.
For Eg:-
Input:-
Group ID Member ID
A  A
A  B
A  C
A  D
X  X
X  D
X  Y
X  Z
M  M
M  N
M  O
P  P
P  Q
Expected Output:-
Group ID Member ID
A  A
A  B
A  C
A  D
X  X
X  D
X  Y
X  Z
Here Member "D" belongs to both the groups and hence both groups are present in the output. The groups with Group ID M and P are not considered in the output as all members belongs to single group only.
Thanks a lot in advance.
Sagar.

terasum 13 posts Joined 10/12
28 Jul 2013

Sagar,

Put your table name instead of in below query and run it.

select * from
where group_id in
( select group_id from
where member_id in
(select member_id from
group by 1
having count(member_id) >1
)
)
order by group_id,member_id;

terasum 13 posts Joined 10/12
28 Jul 2013

give your table name after the from clause in above sql

pavan.polish 18 posts Joined 09/12
28 Jul 2013

Hi terasum,
Thanks a lot for the solution. Is there any other simple way to achieve the same.
Regards,
Sagar

pavan.polish 18 posts Joined 09/12
31 Jul 2013

Hi All,
I got some additional requirement as below:
I have data like below with Group ID and Member ID. I want to extract both Group ID and Member ID details for which a member belongs to more than one group.
 
For Eg:-
Input:-
Group ID Member ID
A  A
A  B
A  C
A  D
X  X
X  D
X  Y
X  Z
M  M
M  N
M  O
P  P
P  Q
Expected Output:-
Group ID Member ID
A  D
X  D
Here Member "D" belongs to both the groups and hence both groups are present in the output. The groups with Group ID M and P are not considered in the output as all members belongs to single group only.
Thanks a lot in advance.
Sagar.

ulrich 816 posts Joined 09/09
01 Aug 2013

 
Hi pavan,
 
try 

select group_id, member_id
from your_table
qualify count(group_id) over (partition by member_id) > 1
;

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.