CODEWITHSUNDEEP
sqlcommon-table-expressionhierarchical-dataself-join
Frisco
Frisco

Reputation: 1

How to create combination between family members from one table

I have one table with 4 columns. The first column contains Group_ID. The 2nd column contains the Contact_ID. Columns 3 and 4 contain First Name and Relationship (respectively). I would like to show all the combinations possible for each group of families(Group ID).

Group_ID Contact_ID F_Name Relationship
426281 928562 Jim Father
426281 876931 Meg Mother
426281 474931 Tom Son
426281 019321 Pam Daughter

The desired output:

Contact_ID Relation_ID
928562 876931 (Jim is Husband to Meg)
928562 474931 (Jim is Father to Tom)
928562 019321 (Jim is Father to Meg)
876931 928562 (Meg is Wife to Jim)
876931 474931 (Meg is Mother to Tom)
876931 019321 (Meg is Mother to Pam)
474931 928562 (Tom is son to Jim)
474931 876931 (Tom is son to Meg)
474931 019321 (Tom is brother to Pam)
019321 928562 (Pam is daughter to Jim)
019321 876931 (Pam is daughter to Meg)
019321 474931 (Pam is sister to Tom)

The original table contains a few 1000 records with many families (Group_IDs).

How do I create the initial SQL to set up the results in a way that I can then loop through the data?

Upvotes: -1

Views: 82

Answers (1)

GMB
GMB

Reputation: 222632

If I follow you correctly, that’s a self-join, that brings all other contacts in the same group:

select t1.contact_id, t2.contact_id as relation_id
from mytable t1
inner join mytable t2
    on  t1.group_id    = t2.group_id
    and t1.contact_id != t2.contact_id
order by t1.contact_id, t2.contact_id

Upvotes: 0

Related Questions