Selecting data from a mapping table using a foreign key

All we need is an easy explanation of the problem, so here it is.

I’m trying to make a simple Login sys where each user should be shown data from a list depending on their user ID Using MS SQL.
After searching for how to do it best i came across Mapping (pretty much making a 3d Table containing both USR_ID and Element_ID, both of course being forign keys)

CREATE TABLE [dbo].[Users] (
[USR_ID]        INT          IDENTITY (1, 1) NOT NULL,
[USR_UserName]  VARCHAR (50) NOT NULL,
[USR_Password]  VARCHAR (50) NOT NULL,
[USR_FirstName] VARCHAR (50) NOT NULL,
[USR_LastName]  VARCHAR (50) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([USR_ID] ASC));

CREATE TABLE [dbo].[GameList] (
[GL_ID]          INT             IDENTITY (1, 1) NOT NULL,
[GL_Title]       NVARCHAR (MAX)  NOT NULL,
[GL_Genre]       NVARCHAR (MAX)  NOT NULL,
[GL_Plattform]   NVARCHAR (MAX)  NOT NULL,
[GL_ReleaseDate] DATETIME2 (7)   NOT NULL,
[GL_Price]       DECIMAL (18, 2) NOT NULL,
CONSTRAINT [PK_GameList] PRIMARY KEY CLUSTERED ([GL_ID] ASC));

CREATE TABLE [dbo].[UserGame] (
[UG_ID]  INT IDENTITY (1, 1) NOT NULL,
[USR_ID] INT NOT NULL,
[GL_ID]  INT NOT NULL,
CONSTRAINT [PK_UserGame] PRIMARY KEY CLUSTERED ([UG_ID] ASC),
CONSTRAINT [FK_USR_ID] FOREIGN KEY ([USR_ID]) REFERENCES [dbo].[Users] ([USR_ID]),
CONSTRAINT [FK_GL_ID] FOREIGN KEY ([GL_ID]) REFERENCES [dbo].[GameList] ([GL_ID]));

Those are how i created the 3 Tables and my Qestion is how can i select all the Games from dbo.GameList that has the same USR_ID
If it was like using USR_ID a normal forign key i wouold’ve probably used something like

SELECT GameList.Title, GameList.Genre, GameList.Plattform, GameList.ReleaseDate, GameList.Price
FROM GameList
INNER JOIN Users ON GameList.USR_ID=Users.USR_ID;

But since it’s my first time using mapping i’m clueless.

Thanks in Advance !!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It is the same

SELECT gl.Title, gl.Genre, gl.Plattform, gl.ReleaseDate, gl.Price
FROM GameList gl
INNER JOIN UserGame ug ON gl.GL_ID = ug.GL_ID
WHERE ug.USR_ID = 10;

As long you have the User_id you don’t need more, but if you also want information from the users table

SELECT gl.Title, gl.Genre, gl.Plattform, gl.ReleaseDate, gl.Price
FROM GameList gl
INNER JOIN UserGame ug ON gl.GL_ID = ug.GL_ID
INNER JOIN Users u ON ug.USR_ID=u.USR_ID
WHERE ug.USR_ID = 10;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply