How do i create a specific one many view using Microsoft SQL Server-CodePudding

I have a master table and a detail table.

Question 1: I would like my view to look like this

master1 row, nulls
master1 row, detail1 row for master1
master1 row, detail2 row for master1
master1 row, detail3 row for master1
master2 row, nulls
master2 row, detail1 row for master2
master2 row, detail2 row for master2

Question 2: how can I insert a column at the beginning of each row that tells me when a row is a master only row and when a row is a master / detail row?

Thanks for any help, it is much appreciated

CodePudding user response：

You can do this with a union query. But you need to specify column names:

select m.pk, m.a, m.b, m.c, d.pk, d.a, d.b, d.c
from master m
join detail d on m.pk = d.fk

union all

select m.pk, m.a, m.b, m.c, null, null, null, null
from master m

order by m.pk, d.pk

In order to determine if a row is a master or detail, just test if d.pk column in the combined result is null or not.