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

Posted by DaNieL on Stack Overflow See other posts from Stack Overflow or by DaNieL
Published on 2010-06-08T08:30:21Z Indexed on 2010/06/08 8:32 UTC
Read the original article Hit count: 313

Filed under:
|
|
|

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql