创建视图sql关联产品的三级类别

产品分三级类别，但有的只有二级类别，现在想用一个视图，将所有的产品和类别关联起来，有三级类的就显示，没有三级类的只显示一二级类名称，三级类别处显示为null

  A表 
oneid      onefolder 
50            testa 
53            testa1 
B表 
twoid      oneid        twofolder 
87            50              testb 
88            50              testb1 
89            53              testb2 
C表 
threeid      twoid        threefolder 
12                87              testc 
13                88              testc1 
product表 
id            oneid      twoid        threeid      proname 
1              50                87          12                产品1 
2              53                89          0                  产品2 
想要如下的结果 
id      proname    onefolder      twofolder      threefolder 
1        产品1        testa              testb              testc 
2        产品2        testa1            testb2            此处是null 
注意一下product表的数据，因为产品2只能二级类别，没有三级，所以第三级类别值为0

SQL code 

create table A(oneid int,onefolder varchar(20))
insert into A select 50 ,’testa’   
insert into A select 53 ,’testa1’

create table B(twoid int,oneid int,twofolder varchar(20))
insert into B select 87 ,50,’testb’   
insert into B select 88 ,50,’testb1’   
insert into B select 89 ,53,’testb2’

create table C(threeid int,twoid int,threefolder varchar(20))
insert into C select 12 ,87  ,’testc’   
insert into C select 13 ,88  ,’testc1’ 

create table product(id int,oneid int,twoid int,threeid int,proname varchar(20))
insert into product select 1,50,87,12,’产品1’   
insert into product select 2,53 ,89 ,0 ,’产品2’

go

create view v_test
as
    select x.id, x.proname,onefolder,twofolder,threefolder
    from product x left join A on x.oneid=A.oneid
        left join B on x.twoid=B.twoid
        left join C on x.threeid=C.threeid
go

select * from v_test

drop view v_test
drop table A,B,C,product

/*
id          proname              onefolder            twofolder            threefolder          
----------- -------------------- -------------------- -------------------- -------------------- 
1           产品1                  testa                testb                testc
2           产品2                  testa1               testb2               NULL

（所影响的行数为 2 行）
*/