subquery with join in same table in mysql

Question

I am trying to fetch some data from my table using join and subquery. My scenario is: I have a table table ABC, with structure as shown

I want the id,name , name of parent and name of spouse ,if any of a member which is not a spouse. The query I tried is:

SELECT E1.name AS memname,
       E2.name AS parentname 
FROM ABC AS E1 
LEFT OUTER JOIN ABC AS E2 ON E2.id = E1.parent_id  
where E1.relation != "Spouse"

This query returns the correct rows for the id,name and parentname of a member. Now I need to rewrite the query so that I'll get the spouse name also, if any member have spouse. Expected output is:

I tried using subquery to select the spouse name, but it was error. Can anyone help me with this? Thanks in advance

Answer 1

You can have one more self-join to get the Spouse name.

SELECT E1.name AS memname,
       E2.name AS parentname,
       E3.name AS spouse 
FROM ABC AS E1 
LEFT OUTER JOIN ABC AS E2 ON E2.id = E1.parent_id  
LEFT OUTER JOIN ABC AS E3 ON E3.parent_id = E1.id AND 
                             E3.relation = 'Spouse'   
where E1.relation <> 'Spouse'