-4

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.

Muhammad Hassan
  • 1,224
  • 5
  • 31
  • 51
  • "not able to cover my question in words" Please try. Also show any parts you can do & give a [mre]. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. [How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/3404097) When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) – philipxy Mar 27 '22 at 08:45
  • "the child count" is clearly not clear, please use enough words. Please try to give a query that gets them. But first, one that gets those children with other relevant data. Etc. – philipxy Mar 27 '22 at 08:49
  • Question updated with a demo output also. – Muhammad Hassan Mar 27 '22 at 14:59
  • Answer added... – Muhammad Hassan Mar 27 '22 at 15:43

1 Answers1

0

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;
Muhammad Hassan
  • 1,224
  • 5
  • 31
  • 51