identifying relations into groups - oracle 10 sql

I have a table (o_relationships) which records relationships between siblings. I need some help to develop some sql which links together the siblings into family groups.

Please see the attached:
-fig1. current output from o_relationships table. SUBJECT_ID and SUBJECT_SURNAME are the subjects details - SIBLING_ID and SIBLING_SURNAME are the details of the subjects sibling/ relationship.
-fig2. Desired output - the output needs to list each subject once and two new columns (the FAMILY_NAME and FAMILY_ID). The family name is a combination of the SUBJECT_SURNAME and the SIBLING_SURNAME (s).

Some examples of how the family name is identified -
-subject_id 319058- the family_name is KHAN/ MCMILLAN (the subjects surname is MCMILLAN and the subjects siblings surname is KHAN).
-subject_id 88307 - the family_name is BROOKFIELD (the subjects surname is BROOKFIELD and the subject does not have any siblings)
-subject_id 14484 - the family_name is GRIMMER (the subjects surname is GRIMMER, the subject has multiple siblings with the surname of GRIMMER)

The family_ID is the unique identifier for the family.

Any help to produce the sql to achieve this result is appreciated. I don’t have the ability to run procedures so this would need to be done in sql.
dl1.xls
tonMachine100Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
Do you need the whole query, or are you just looking to sort out the surname thing? If it's just surnamed, you could do something like this:

CASE SIBLING_SURNAME WHEN '' THEN SUBJECT_SURNAME WHEN SUBJECT_SURNAME THEN SUBJECT_SURNAME ELSE SUBJECT_SURNAME + '/' + SIBLING_SURNAME END as FamilyName

This compares that surname of the subject and sibling and then prints out a family name - just the surname if they match or the person doesn't have any siblings, or a combination if the surnames are different.

Does that do what you need?
0
All Courses

From novice to tech pro — start learning today.