Solved

identifying relations into groups - oracle 10 sql

Posted on 2011-03-17
1
268 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:tonMachine100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 35211408
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question