CODEWITHSUNDEEP
mysqljoinsubquery
Aishwaryas
Aishwaryas

Reputation: 643

subquery with join in same table in mysql

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

ID NAME PARENT RELATION

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:

enter image description here

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

Upvotes: 3

Views: 1433

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

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'

Upvotes: 3

Related Questions