TSQL For foreign key and Primary key

Try this:

declare @tbl1 table (id int, ArmourId int, ShieldId int);
declare @tbl2 table (id int, MaterialName varchar(100), IsArmour bit);

insert @tbl1 values( 1, 10, 11), (2, 12, 11), (3, 12, 13)
insert @tbl2 values( 10, 'A1', 1 ), (11, 'S1', 0), (12, 'A2', 1), (13, 'S2', 0)

;with set1 as 
(
select a.ArmourId, a.id, a.ShieldId, b.id as bid, b.IsArmour, b.MaterialName 
from 
        @tbl1 a
  join 
        @tbl2 b
        on 
          a.ArmourId = b.id
where 
        b.IsArmour = 1
), set2 as
(
select a.ArmourId, a.id, a.ShieldId, b.id as bid, b.IsArmour, b.MaterialName
from 
        @tbl1 a
  join 
        @tbl2 b
        on 
          a.ShieldId = b.id
where 
        b.IsArmour = 0
)
select 
        x.id,
        x.MaterialName,
        y.MaterialName
from set1 x
  cross join set2 y
where 
    x.ArmourId = y.ArmourId
  and 
    x.id = y.id

---

Saeid Hasani, sqldevelop.wordpress.com



Download Books Online for SQL Server 2012