Create a additional row from join sql?-CodePudding

Home > Software design > Create a additional row from join sql?

Create a additional row from join sql?

Time：10-15

I have 2 tables: Person and House with 1-n relation. I want to the result return as picture below:

Row always have a Person column with a null House column.

Thanks.

CodePudding user response：

you can use unionall to join the result set with person table something like

select p.name,h.name as housename from person p join house h on p.id=h.personid 
union all (select name,null from person)
order by name,housename