T-Sql（四）表关联和视图（view）

select t2.*         --表自关联
from Tree t1
inner join Tree t2
on t1.NO=t2.ParentNo
where t1.Name='Node1_2';

select * from tree      --子嵌套查询
where ParentNo = (select NO from tree where Name = 'Node1_2');

select * from tree 
where parentNo in (select no from tree where Name = 'Node1_2');

select t2.*         --左关联
from Tree t1
left join Tree t2
on t1.NO=t2.ParentNo
where t1.Name='Node1_2';

select t2.*         --右关联
from Tree t1
right join Tree t2
on t1.NO=t2.ParentNo
where t1.Name='Node1_2';

create view Production.vw_Product
as 
    select t1.* from Production.Product t1
    left outer join Production.ProductModel t2 on t2.ProductModelID=t1.ProductModelID
    left outer join Production.ProductSubcategory t3 on t3.ProductSubcategoryID=t1.ProductSubcategoryID
    left outer join Production.UnitMeasure t4 on t4.UnitMeasureCode=t1.SizeUnitMeasureCode and t4.UnitMeasureCode=t1.SizeUnitMeasureCode
    --order by t1.ProductID
    

select *from Production.vw_Product