Join with adding new row

Question

I have a query which returns next table with name first_table:

Name	ID
First	1
Second	2

And I need to join another table named second_table:

ID	ParentID
22	1
33	323

By the columns first_table."ID" = second_table."ParentID", so if first_table_id exists, I need to add one more row with its first_table."Name" value

So the result should be:

Name	ID
First	1
First	22
Second	2

Answer 1

You can do something like this (result here)

select t1.name,t1.id
from t1 join t2 on t1.id = t2.parent_id
union
select t1.name,t2.id
from t1 join t2 on t1.id = t2.parent_id
union
select t1.name,t1.id
from t1 
where t1.id not in (select parent_id from t2)
order by name,id