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.