Many to Many Relationship - Displaying rows in multiple tables

Question

First question here.

So a bit of background, I'm pretty new in the development field and am creating a Database-first MVC app that basically functions as a log of video games and their genres for gamers to discover new and old games based on the genres that peak their interest. This necessitates a many-to-many relationship given one game may be classified under several genres and one genre can have multiple games classified under it. As it currently stands in my SQL Database, I have three tables - Game, Genre, and a linking table. The linking table has a FK for both the GameID as well as the GenreID. To my understanding (or lack thereof) since there's a linking table and no actual FK for genre in the Game table itself, how would I be able display the multiple genres applied to each game on each individual game's info page? Is there something I'm missing that I could include in one of the tables, a possible SQL query that would help, or is there some kind of hard coding I can do in Visual Studio once I import my DB in to program the rest of the app?

My database looks like:

CREATE TABLE Game 
(   
    GameID INT IDENTITY(1, 1) NOT NULL,   
    Name NVARCHAR(50) NOT NULL,   
    GameDescription NVARCHAR(200) NULL   
)

CREATE TABLE Genre 
(
    GenreID INT IDENTITY(1, 1) NOT NULL,   
    Genre NVARCHAR(20) NOT NULL   
)

CREATE TABLE GameGenre 
(
     GameGenreID INT IDENTITY(1, 1) NOT NULL,  
     FOREIGN KEY ('GameID') REFERENCES Cities('GameID'),   
     FOREIGN KEY ('GenreID') REFERENCES Cities('GenreID')   
)

Thank you in advance. Any and all advice is greatly appreciated!

Your GameGenre table probably does not need the GameGenreID column. You could simply define a compound primary key which includes both the GameID and GenreID columns. This has the benefit of preventing duplicate mappings of gameID and genreID. — Tom Drake, Oct 15 '18 at 17:36

score 2 · Accepted Answer · answered Oct 15 '18 at 17:26

Good question. Although simplified, I think your approach is sound and it is obvious you have done your homework.

Once your tables are constructed and filled appropriately, you will need to execute SQL statements to properly grab what you are looking for.

Assuming you have three tables listed above like so:

| Game        | Genre   | GameGenre   |
|-------------|---------|-------------|
| GameID      | GenreID | GameGenreID |
| Name        | Genre   | GameID      |
| Description |         | GenreID     |

You would "link" them by going through your GameGenre table, called a bridge table.

So if you wanted to see ALL of the genres that a particular game is associated with, some example code could be:

SELECT
    Game.GameID,
    Game.Name
    GameGenre.GenreID,
    Genre.Genre

FROM
    Game
        INNER JOIN GameGenre ON Game.GameID = GameGenre.GameID
        INNER JOIN Genre ON GameGenre.GenreID = Genre.GenreID

WHERE
    Game.Name = 'Metal Gear Solid'

What you are doing is starting with a particular game, and then using that game's ID to go the bridge table. Once you have properly joined into the bridge table, you can then join into the Genre table to grab information there.

If you wanted to start with a Genre and see all of the associated games, you could simply reverse the process. You would start in Genres and join into the bridge table, and then from the bridge table join into Game. Some example pseudocode is below:

SELECT
    Genre.GenreID,
    Genre.Genre,
    Game.Name

FROM
    Genre
        INNER JOIN GameGenre ON Genre.GenreID = GameGenre.GenreID
        INNER JOIN Game ON GameGenre.GameID = Game.GameID

WHERE
    Genre.Genre = 'Espionage'

Hopefully this helps! You are definitely on the right track, and I think you are asking the right questions. If you need to understand joins better, suggest you start here:

score 0 · Answer 2 · answered Oct 15 '18 at 17:26

0

Just "join" all of your 3 tables. Reference can be found here

answered Oct 15 '18 at 17:26

Hendro Febrian

202
1
3
12

score 0 · Answer 3 · answered Oct 15 '18 at 18:25

Can you try below, and let me know if you have any questions

SELECT G.GameID, G.Name, G.GameDescription, Genre.Genre
FROM Game G
LEFT JOIN GameGenre GG ON G.GameID = GG.GameID
LEFT JOIN Genre Genre ON GG.GenreID = Genre.GenreID 
-- WHERE if you have any where statement

Many to Many Relationship - Displaying rows in multiple tables

3 Answers3