0

For context, look here:

Database Model to Represent Families, Households, and Relationships

Basically, I am trying to map relationships between people and households without tying relationships and households to a single "head of household". So far I think my model works well. Here's what works:

SELECT person.person_id,person.first_name, relationship.person_id_a, relationship.person_id_b, relationship.relationship_cd
FROM person, relationship
WHERE person_id = 1
AND (person.person_id = relationship.person_id_a
OR person.person_id = relationship.person_id_b)
AND relationship.relationship_cd <> 0;

results in:

"person_id" "first_name" "person_id_a" "person_id_b" "relationship_cd"
"1" "Joe"   "1" "2" "8 - Spouse"
"1" "Joe"   "1" "3" "7 - Sibling"

So I know that Joe (person 1) has a spouse (person 2) and a sibling (person 3). Is there a way for me to use attach the name (or other info) of the people Joe has a relationship with to the output? That is, query 1 tells me who Joe has relationships with (from a join with the relationship table), can I nest another query in such a way that I can then pull the name of that person (from a join back to another record from the person table)? So that it looks like this:

"person_id" "first_name" "person_id_b" "relationship_cd" "related_person_name"
"1" "Joe" "2" "8 - Spouse" "Susan"
"1" "Joe" "3" "7 - Sibling" "Bob"
Community
  • 1
  • 1
imfm
  • 119
  • 7
  • Yes you have to join relationship back to person a second time. But this time on relationship.person_ID_b alias person tablename as p2 or something and then show p2.first_name. Think about it for a min. The Join to display the name is on the person and relationship. To get the name of the 2nd person you have to take their ID and go back to person to get their name. – xQbert May 08 '15 at 18:40

1 Answers1

1

I would select from relationship where person_id_a or person_id_b equals ID of your person (Joe) and then join twice person for both a and b persons in relationship. For example:

SELECT 
r.person_id_a, 
a.first_name as first_name_a,
r.person_id_b,
b.first_name as first_name_b
r.relationship_cd 
FROM relationship r
JOIN person a on a.person_id = r.person_id_a
JOIN person b on b.person_id = r.person_id_b
WHERE r.person_id_a = 1
OR r.person_id_b = 1 
AND relationship.relationship_cd <> 0;

This query will return all relationships for person with person_id = 1 with person_a and person_b first names.

If you want to get the result in the form like in your answer, query could be like this:

    SELECT 
        p.person_id,
        case when p.person_id = r.person_id_a then a.first_name else b.first_name end as first_name,
        case when p.person_id = r.person_id_a then b.person_id else a.person_id end as person_id_related,
        case when p.person_id = r.person_id_a then b.first_name else a.first_name end as first_name_related,
        r.relationship_cd 
FROM person p
JOIN relationship r ON r.person_id_a = p.person_id OR r.person_id_b = p.person_id
JOIN person a on a.person_id = r.person_id_a
JOIN person b on b.person_id = r.person_id_b
WHERE p.person_id = 1 
        AND relationship.relationship_cd <> 0;
denied
  • 598
  • 3
  • 9
  • Very simple and elegant solution. I've never really taken the time to learn joins. Guess it's about time. :) Thanks! – imfm May 08 '15 at 20:36