Solved

identifying relations into groups - oracle 10 sql

Posted on 2011-03-17
1
260 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
1 Comment
 
LVL 28

Accepted Solution

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now