MYSQL query to pull data from 2 different records in same table based on query result

Question

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"

Answer 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;