如何内连接多列？

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.

有一个Genres表有Genre名称和Genre ID，然后有一个Genrebridge表有genreID1，GenreID2，GenreID3，GenreID4，GenreID5，SongID，AlbumID和ArtistID。

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

如何内部连接每个GenreID字段，因此类型名称链接到类型桥表

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

2 个解决方案

#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.

除了使用注释中提到的相同别名外，表格GenreBridge未正确标准化。要规范化表格，最好在表格上放置一个GenreID列，然后根据需要插入尽可能多的行来为专辑的所有类型进行建模。这也将解除对每张专辑类型数量的任意限制。

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.

作为旁注，您的原始程序似乎根本不使用Genre（在选择列表或过滤器中），因此无需加入它，您将不需要DISTINCT。

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

正如您所注意到的，您现在面临着连接问题，具体取决于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.:

假设你一直使用INT主键，我会将表GenreBridge标准化为Albums和Genres之间的许多链接表，同时将其重命名为AlbumGenre以更好地反映许多：许多惯例，例如：

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.)

您的程序需要强制执行1到5种类型的限制（即在插入AlbumnGenre记录链接之前，确保此列的行少于5行。）

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).

要列出专辑的所有类型，您只需要将Albumn加入AlbumGenre并在where子句中按AlbumnId过滤（它将返回与类型一样多的行）。

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

如上所述，不需要DISTINCT或加入到流派：

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

#2

@StuartLC gives some good feedback that I would seriously consider. But if you intend to leave your database schema as is, then this query should work:

@StuartLC提供了一些我认真考虑的好反馈。但是，如果您打算按原样保留数据库模式，那么此查询应该有效：

SELECT          a.AlbumTitle, 
                art.ArtistName, 
                a.AvgRatingNBR, 
                a.OriginalPrice, 
                a.DiscountPrice,
                g1.GenreName,
                g2.GenreName,
                g3.GenreName,
                g4.GenreName,
                g5.GenreName
FROM            Albums a 
INNER JOIN      Artists art on a.ArtistID = art.ArtistID
INNER JOIN      GenreBridge gb on gb.AlbumID = a.AlbumID
LEFT OUTER JOIN Genres g1 on g1.GenreID = gb.GenreID1
LEFT OUTER JOIN Genres g2 on g2.GenreID = gb.GenreID2
LEFT OUTER JOIN Genres g3 on g3.GenreID = gb.GenreID3
LEFT OUTER JOIN Genres g4 on g4.GenreID = gb.GenreID4
LEFT OUTER JOIN Genres g5 on g5.GenreID = gb.GenreID5
WHERE           a.AlbumTitle like ('%' + @albumname + '%')
    AND         art.ArtistName like ('%' + @artistname + '%')

#1