主表:MID Name
1 op1
2 op2
3 op3
4 op4
子表1:MID SID
1 1
1 2
2 1
2 2
2 3
3 2
3 1
子表2:MID UID
1 2
2 2
1 1
写一个视图把主表,子表1,子表2的记录全部显示出来该如何写
1 op1
2 op2
3 op3
4 op4
子表1:MID SID
1 1
1 2
2 1
2 2
2 3
3 2
3 1
子表2:MID UID
1 2
2 2
1 1
写一个视图把主表,子表1,子表2的记录全部显示出来该如何写
union all
select * from T2
union all
select * from T3
as
select a.MID,a.Name,b.SID,c.UID from 主表 a
right join table1 b
on a.MID=b.MID
left join 子表2 c
on a.MID=c.MID
left join table_a a
on m.MID=a.MID
left join table_b b
on m.MID=b.MID
这么写对吗
left join table_a a
on m.MID=a.MID
left join table_b b
on m.MID=b.MID
select dbo.tb.MID,dbo.tb.name,dbo.ctb1.SID,dbo.ctb2.UID
from dbo.tb FULL OUTER join dbo.ctb1 on dbo.tb.MID =dbo.ctb1.MID left join dbo.ctb2 on dbo.tb.MID = dbo.ctb2.MID
select *
from main
drop view main1 op1 1 1
1 op1 1 2
2 op2 1 2
2 op2 2 2
2 op2 3 2
3 op3 1 NULL
3 op3 2 NULL
4 op4 NULL NULL