How do I create a view in SQL Server 2014 to see data from cross referenced tables where some data is missing? -

i trying generate report normalized database tables a, a_xref_b, , b.

create table (a_rid int primary key, a1 varchar(20), );  create table b (b_rid int primary key, b1 varchar(20), b2 varchar(20));  create table a_xref_b (a_rid int, b_rid int,xref_type int constraint fk_a foreign key (a_rid) references (a_rid), constraint fk_b foreign key (b_rid) references b (b_rid));  insert values (1,'john'), (2,'sue') insert b values (1,'atlanta','ga'), (2,'macon','ga'), (3,'opp','al') insert a_xref_b values (1,1,1), (1,2,2), (2,3,1) 

table , b cross-referenced , have cross reference has type, defines data in b represents.

scenario - john provided main city , alternate. sue provided main city.

i trying write view load page has name, main city, , alternate.

name   b_rid_t1  city_t1   st_1  b_rid_2   city_t2  st_t2 john   1         atlanta   ga    2         macon    ga sue    3         opp       al    null      null     null 

i tried this

select    a.a1,    b_1.b_rid,    b_1.b1,    b_1.b2,    b_2.b_rid,    b_2.b1,    b_2.b2               left join    a_xref_b xf1        on a.a_rid = xf1.a_rid       inner join    b b_1        on xf1.b_rid = b_1.b_rid        , xf1.xref_type = 1      left join    a_xref_b xf2        on a.a_rid = xf2.a_rid       inner join    b b_2        on xf2.b_rid = b_2.b_rid        , xf2.xref_type = 2 

however drops sue's record. if change left joins xref b, duplicate records.

the real world scenario contact table cross referenced address table , xref table has type field. we're trying build view load page.

try this, should add xref_type condition a_xref_b table join.

select    a.a1,    b_1.b_rid,    b_1.b1,    b_1.b2,    b_2.b_rid,    b_2.b1,    b_2.b2               left join a_xref_b xf1 on a.a_rid = xf1.a_rid          , xf1.xref_type = 1      left join b b_1 on xf1.b_rid = b_1.b_rid        left join a_xref_b xf2 on a.a_rid = xf2.a_rid          , xf2.xref_type = 2 left join b b_2 on xf2.b_rid = b_2.b_rid