How to inner join multiple columns?

Question

I am trying to write a stored procedure for a keyword search. The way we have our DB set up.

There is a Genres table that has Genre names and Genre ID's, then there is a Genrebridge table which has genreID1, GenreID2, GenreID3, GenreID4, GenreID5, SongID, AlbumID and ArtistID.

How do I inner join each of the GenreID fields, so the Genre name is linked to the Genre Bridge table

ALTER PROCEDURE [dbo].[usp_album_search_keyword_AlbumNameANDArtistName]
(
    -- Add the parameters for the stored procedure here
    @albumname  varchar(255),
    @artistname varchar(255)
)
As
Begin
Select 
    Distinct a.AlbumTitle, art.ArtistName, a.AvgRatingNBR, a.OriginalPrice, a.DiscountPrice
FROM 
    Albums a 
    inner join Artists art on a.ArtistID = art.ArtistID
    inner join GenreBridge gb on gb.AlbumID = a.AlbumID
    inner join Genres g on g.GenreID = gb.GenreID1
    inner join genres g on g.GenreID = gb.genreID2
    inner join genres g on g.GenreID = gb.GenreID3
    inner join genres g on g.GenreID = gb.GenreID4
    inner join genres g on g.GenreID = gb.GenreID5

    where a.AlbumTitle like '%' + @albumname + '%' 
    and art.ArtistName like '%' + @artistname + '%'
End

Answer 1

Other than using the same aliases as noted in the comments, the table GenreBridge isn't correctly normalized. To normalize the table, it would be better to have a single GenreID column on the table, and then insert as many rows as needed to model all genres of an album. This would also lift the arbitrary restriction on the number of genres per album.

As a side note, your original procedure doesn't seem to use Genre at all (in either the select list or where filter), so hence no need to join to it, and you won't need the DISTINCT.

As you've noted, you now face issues with joins depending on the number of valid genre columns present on AlbumGenre

Assuming you've used INT primary keys throughout, I would normalize the table GenreBridge to be a many:many link table between Albums and Genres, and at the same time rename it AlbumGenre to better reflect the many:many convention, e.g.:

CREATE TABLE dbo.AlbumGenre
(
   AlbumId INT NOT NULL,
   GenreId INT NOT NULL,

   CONSTRAINT PK_AlbumnGenre PRIMARY KEY(AlbumId, GenreId),
   CONSTRAINT FK_AlbumnGenre_Albumn FOREIGN KEY(AlbumId) REFERENCES Albums(AlbumId),
   CONSTRAINT FK_AlbumnGenre_Genre FOREIGN KEY(GenreId) REFERENCES Genres(GenreId)
);

The restriction that there can be between 1 and 5 genres will need to be enforced by your program (i.e. before inserting an AlbumnGenre record link, ensure that there are less than 5 rows for this albumn.)

To list all genres of an album, you would just need to join Albumn to the AlbumGenre and filter by AlbumnId in the where clause (it will return as many rows as there are genres).

As mentioned, no need for DISTINCT or joining to Genre:

ALTER PROCEDURE [dbo].[usp_album_search_keyword_AlbumNameANDArtistName]
(
    -- Add the parameters for the stored procedure here
    @albumname  varchar(255),
    @artistname varchar(255)
)
As
Begin
Select a.AlbumTitle, art.ArtistName, a.AvgRatingNBR, a.OriginalPrice, a.DiscountPrice
FROM 
    Albums a 
    inner join Artists art on a.ArtistID = art.ArtistID
    where a.AlbumTitle like '%' + @albumname + '%' 
    and art.ArtistName like '%' + @artistname + '%'
End