Selecting data from a mapping table using a foreign key

Question

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 !!

Answer 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;

• Virgal K: Thanks for the Answer, but could you maybe tell me why did user "Where WHERE ug.USR_ID = 10;" or was it just an example ?
• nbk: yes, this is only an examle. this was only if you want the all games from user_id 10
• Virgal K: Oh i get it now, Thanks !!