Selecting groups whose member belongs to multiple groups

Database

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.

5 REPLIES

Re: Selecting groups whose member belongs to multiple groups

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;

Re: Selecting groups whose member belongs to multiple groups

give your table name after the from clause in above sql

Re: Selecting groups whose member belongs to multiple groups

Hi terasum,

Thanks a lot for the solution. Is there any other simple way to achieve the same.

Regards,

Sagar

Re: Selecting groups whose member belongs to multiple groups

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.

N/A

Re: Selecting groups whose member belongs to multiple groups

Hi pavan,

 

try 

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