SQL Server: Joining three tables while showing null matches

Question

I have spent a couple of hours trying to figure out this particular join. I have these three SQL Server tables (assume all the requisite indexes and foreign keys):

create table [mykey] (keyname varchar(32) not null);
go
create table [myinstance] (
instancename varchar(48) not null);
go
create table [mypermissions] (
keyname varchar(32),
instancename varchar(48),
permission int not null);
go
insert into [mykey] (keyname) values ('test_key_1'), ('test_key_2'), ('test_key_3');
GO
insert into [myinstance] (instancename) values ('sqlexpress'), ('std2008'), ('ent2012');
GO
insert into mypermissions (keyname, instancename, permission) values
    ('test_key_1', 'sqlexpress', 1),
    ('test_key_1', 'std2008', 0),
    ('test_key_2', 'ent2012', 1),
    ('test_key_2', 'sqlexpress', 0)
GO

I am trying to create a join that shows all the permissions for keynames while also showing where specific permissions are missing. I would like the output to look like this (order unimportant):

keyname     instancename   permission
----------  ------------   ----------
test_key_1  sqlexpress     1
test_key_1  std2008        0
test_key_1  ent2012        NULL
test_key_2  ent2012        1
test_key_2  sqlexpress     0
test_key_2  std2008        NULL
test_key_3  sqlexpress     NULL
test_key_3  std2008        NULL
test_key_3  ent2012        NULL

but instead it looks like this:

keyname     instancename   permission
----------  ------------   ----------
test_key_1  sqlexpress     1
test_key_1  std2008        0
test_key_2  ent2012        1
test_key_2  sqlexpress     0
test_key_3  NULL           NULL

I have spent a couple of hours with all sorts of joins with no luck. Maybe what I want to do is impossible. Here is the most recent one I tried:

select k.keyname, p.instancename, p.permission from mykey k
    full join mypermissions p on (k.keyname = p.keyname)
    full join myinstance i on p.instancename = i.instancename

Is what I am trying to do possible?

Answer 1

You need to use CROSS JOIN to get all combinations of keyname and instancename before you join the third table. Look at this query

SELECT *
FROM myinstance mi
CROSS JOIN mykey mk

This will output

instancename    keyname
------------    ----------
sqlexpress      test_key_1
std2008         test_key_1
ent2012         test_key_1
sqlexpress      test_key_2
std2008         test_key_2
ent2012         test_key_2
sqlexpress      test_key_3
std2008         test_key_3
ent2012         test_key_3

You can use LEFT JOIN to the third table and extract the columns required as shown in the below query:

SELECT mk.keyname, mi.instancename, mp.permission
FROM myinstance mi
CROSS JOIN mykey mk
LEFT JOIN mypermissions mp ON mk.keyname = mp.keyname AND mi.instancename = mp.instancename
ORDER BY mk.keyname, mi.instancename