Append parent table column value to child table column in sql

Question

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.

Answer 1

You can use GROUP BY Name to check names with count(*) > 1 and then do a LEFT JOIN to append T1 names appropriately

Sample Data

CREATE TABLE Table1
    ([ID] int, [Name] varchar(4));

INSERT INTO Table1
    ([ID], [Name])
VALUES
    (1, 'Adam'),
    (2, 'Eve');

CREATE TABLE Table2
    ([ID] int, [FK_ID] int, [Name] varchar(7));

INSERT INTO Table2
    ([ID], [FK_ID], [Name])
VALUES
    (1, 1, 'Chris'),
    (2, 1, 'Austin'),
    (3, 1, 'Steve'),
    (4, 2, 'Charles'),
    (5, 2, 'Erik'),
    (6, 2, 'Austin');

Query

SELECT
    T2.ID,
    T2.Name + CASE WHEN T3.Name IS NOT NULL THEN ' ' + T1.Name ELSE '' END as Name
FROM Table2 T2
INNER JOIN Table1 T1 ON T2.[FK_ID] = T1.id
LEFT JOIN (SELECT Name FROM Table2 GROUP BY Name HAVING COUNT(*) > 1) T3 ON T2.Name = T3.Name

Output

ID  Name
1   Chris
2   Austin Adam
3   Steve
4   Charles
5   Erik
6   Austin Eve

Answer 2

You can use a window count to determine whether a name is repeated or not:

SELECT t2.ID, 
       CONCAT(t2.Name,
          IIF(COUNT(t2.Name) OVER(PARTITION BY t2.Name) > 1, 
              COALESCE(' ' + t1.Name, ''), 
              ''))
FROM TABLE2 AS t2
LEFT JOIN TABLE1 AS t1 ON t2.FK_ID = t1.ID