Assume I have two tables, as below:
Table 1
ID Name
------------------
1 Adam
2 Eve
Table 2
ID FK_ID (Table 1) Name
--------------------------------------------
1 1 Chris
2 1 Austin
3 1 Steve
4 2 Charles
5 2 Erik
6 2 Austin
Required table as Result
ID Name
-----------------
1 Chris
2 Austin Adam
3 Steve
4 Charles
5 Erik
6 Austin Eve
Notice here, in the resulting table I want repeated "Austin" appended with "Adam/Eve" from parent table (i.e. Table 1), depending on "FK_ID". I want to do this in SQL. Any idea/help would really be appreciated.