Sql query listing Fathers and childs with joins, how to distinct them?

Question

Having those tables:

table_n1:
| t1_id | t1_name |
|   1   | foo     |

table_n2:
| t2_id | t1_id   | t2_name |
|   1   |   1     | bar     |

I need a query that gives me two result:

| names     |
| foo       |
| foo / bar |

But i cant figure out the right way. I wrote this one:

SELECT
    CONCAT_WS(' / ', table_n1.t1_name, table_n2.t2_name) AS names
FROM
    table_n1
        LEFT JOIN table_n2 ON table_n2.t1_id = table_n1.t1_id

that works for an half: this only return the 2° row (in the example above):

| names     |
| foo - bar |

This query return the 'father' (table_n1) name only when it doesnt have 'childs' (table_n2).

How can i fix it?

Answer 1

Using a UNION and changing the LEFT JOIN to an INNER JOIN should give you the correct result.

SELECT  table_n1.t1_name AS names 
FROM    table_n1 
UNION ALL
SELECT  CONCAT_WS(' / ', table_n1.t1_name, table_n2.t2_name) AS names 
FROM    table_n1 
        INNER JOIN table_n2 ON table_n2.t1_id = table_n1.t1_id