Solved

identifying relations into groups - oracle 10 sql

Posted on 2011-03-17
1
272 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 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