Join Two Table With Matching First Table Row With 2 Rows Of Second Table And Get All 3 Rows In One L-CodePudding

My question is a little confusing as I will explain with a simple example. I want to join a table in two columns. For example, Below I have two tables with the given structure.

Table_01:

ID        Type        Name        Gender
-----------------------------------------
1        Parent    Jhon Cena       Male
2        Tutor       Paige        Female
3        Tutor       Nikki        Female
4        Parent    The Rock        Male
5        Parent    Big Show        Male
6        Tutor       Brie         Female

Table_02:

ID    Tutor_ID    Parent_ID     Name        Gender
----------------------------------------------------
1         2           1        Oliver        Male
2         3           1         Emma        Female
3         3           4        Sophia       Female
4         7           5        George        Male
5         2           4       Isabella      Female
6         6           7        Arthur        Male

Note: The Tutor_ID & Parent_ID columns from Table_02 are the primary key of Table_01 that I have to match.

Now I want to run a query that will pick the Parent and Tutor of the Child(Table_02) in one row as per the Table_02 rows count and where there is no Parent and Tutor is available then leave the data blank but pickup the child data. The example output should be like this.

Output:

T2.ID    T2.Name     T2.Gender     T1.ParentName     T1.T1_TutorName
--------------------------------------------------------------------
1        Oliver         Male        Jhon Cena            Paige 
2         Emma         Female       Jhon Cena            Nikki
3        Sophia        Female       The Rock             Nikki
4        George         Male        Big Show           --NULL--
5       Isabella       Female       The Rock             Paige
6        Arthur         Male        --NULL--             Brie

So Is this possible or do I have to make two tables for Parents and Tutors that is the worst-case scenario for me.

CodePudding user response：

ANSWER:

So I tried and here is the answer...

SELECT * FROM Table_02 as T2, Table_01 as P, (SELECT * FROM Table_01 WHERE Type = 'Tutor') as T WHERE T2.Parent_ID = P.ID AND T2.Tutor_ID = T.ID;

And here is the answer with LEFT JOIN...

SELECT *
FROM Table_02 as T2
LEFT JOIN Table_01 as P 
ON T2.Parent_ID = P.ID
LEFT JOIN (SELECT * FROM Table_01 WHERE Type = 'Tutor') as T
ON T2.Tutor_ID = T.ID;