Trouble using multiple joins

Transact-SQL
1

I am using the following tables, created using the following code:
create table users (userID integer identity(1,1), username varchar(50) UNIQUE, password varchar(50), email varchar(254))
create table admins (userID integer PRIMARY KEY, level integer)
create table userkeys (userID integer, gamekey varchar(16) PRIMARY KEY)
create table gamekeys (gamekey varchar(16) PRIMARY KEY, productID integer)
create table products (productID integer IDENTITY(1,1), productName varchar(100))

I need to get information about a specific gamekey, namely, its productName, and, if it's already in use in the userkeys db, the username of that user.

I can already get such information when I do a few inner joins, as follows:
select users.username, products.productName from products
inner join gamekeys on gamekeys.productID = products.productID
inner join userkeys on gamekeys.gamekey = userkeys.gamekey
inner join users on users.userID = userkeys.userID
where userkeys.gamekey ='specifickey';

My current problem is thus: If the key already exists and is linked to a user, I get the productName and the username without any hassle. Only, when no user owns that key I get absolutely no results.
I know the answer should normally be an outer join, but I can't for the love of me find where to place it in my query.

2

Would a left join work?

3

Nope, the test returns no values for both unless the key is already assigned to a user.

If the key exists but isn't assigned, my query returns no rows.

When the key is assigned the results are perfect.

For test purposes, I assigned a non-existent key.

select users.username, products.productName
from products inner join gamekeys on gamekeys.productID = products.productID
RIGHT join userkeys on gamekeys.gamekey = userkeys.gamekey
inner join users on users.userID = userkeys.userID
where userkeys.gamekey ='inviskey';

Actually returns the correct username, and a null value for the productID, which is what I'd expect.

I'd want this to be reversed. For it to return product info and a null user info when the key isn't assigned but exists.

4 
-- assumes there is a row in gamekeys for every product key
SELECT
	u.username,
	p.productName
FROM
	Products p
	INNER JOIN gamekeys g ON
		g.productId = p.productID
	LEFT JOIN userkeys uk ON
		uk.gamekey = g.gamekey
	LEFT JOIN users u ON 
		u.userID = uk.userID
WHERE
	g.gamekey = 'specifickey'
5

Your assumption is correct.

In fact, for greater clarity, here is the current test DB's full contents:

users (userID, username, password, email)
1, 'username', 'password', 'brucewayne@gotham.gov'
2, 'kicked', 'password', 'doodle@hotmail.com'

products(productID, productName)
1, 'Remember that one game'

gamekeys (gamekey, productID)
'2222222222222222', 1
'1234567812345678', 1
'8765432187654321', 1

userkeys (userID, gamekey)
1, '1234567812345678'
2, '8765432187654321'

My results are as follows.
[...] where g.gamekey = '1234567812345678'
user: username. Product: Remember that one game
[...] where g.gamekey = '2222222222222222'
No rows returned.

6

You must be using a query different from what I posted or the data or table structure is different from what you just posted. With the query I posted when run against the data that you posted, it should return one row which would be:

username    productName
NULL        Remember that one game
1 Like
7

I just found my mistake.
A blatant one too!
...teaches you not to code past 3 AM.

I was searching for userkeys.gamekey instead of gamekeys.gamekey.... Which returned no rows if no users was using it, as it should.

Thanks for everyone's patience.
Thanks for telling me to check my query against yours once more. I could've spent hours searching for a single word...