选择 3 个表，具有多个"on clause"影响 9 行，只有一个寄存器？

Question

我有这 3 个表：注册、品牌和服装，其中注册从品牌下拉列表和服装下拉列表中保存 int 数字，但在选择中我不想要 int 值，而是各自表代表的值。（IdBrand 1 = Nike，IdBrand 2 = Adidas）。它正在"工作"，但我不知道我是否做错了什么，因为当我在 Sql Server 上尝试代码"新查询"时，我有 9 行，导致即使我在 RegisterTable 上只有一个寄存器，这更像是一个"逻辑"问题，在"选择显示"上有很多行正常吗？

《守则》

select Register.*, Clothing.ClothingName, Brand.BrandName
from Register
inner join Clothing 
on RegisterClothingId1 = ClothingId
or RegisterClothingId2 = ClothingId
or RegisterClothingId3 = ClothingId
inner join Brand
on RegisterBrandId1 = BrandId
or RegisterBrandId2 = BrandId
or RegisterBrandId3 = BrandId

我也尝试使用"and"而不是"or"，但它影响/返回零行。同样，此代码"正在工作"。我只是不知道只有一个寄存器产生这么多行是否正常。因为如果 1 个寄存器给出 9 行，我想知道 100 个寄存器会给出 900 行例如。谢谢。

该表只有 3 个品牌列和 3 个服装列，这些

列与其他 2 个表固有

寄存器表

CREATE TABLE [dbo].[Register] (
    [RegisterId]            INT             IDENTITY (1, 1) NOT NULL,
    [RegisterPersonId]      INT             NOT NULL,
    [RegisterPersonNote]    NCHAR (60)      NULL,
    [RegisterCareerId]      INT             NOT NULL,
    [RegisterEvent]         NCHAR (60)      NOT NULL,
    [RegisterEventYear]     INT             NOT NULL,
    [RegisterImgDressed]    VARBINARY (MAX) NOT NULL,
    [RegisterClothingId1]   INT             NOT NULL,
    [RegisterBrandId1]      INT             NOT NULL,
    [RegisterClothingName1] NCHAR (60)      NOT NULL,
    [RegisterClothingImg1]  VARBINARY (MAX) NOT NULL,
    [RegisterClothingId2]   INT             NOT NULL,
    [RegisterBrandId2]      INT             NOT NULL,
    [RegisterClothingName2] NCHAR (60)      NOT NULL,
    [RegisterClothingImg2]  VARBINARY (MAX) NOT NULL,
    [RegisterClothingId3]   INT             NOT NULL,
    [RegisterBrandId3]      INT             NOT NULL,
    [RegisterClothingName3] NCHAR (60)      NOT NULL,
    [RegisterClothingImg3]  VARBINARY (MAX) NOT NULL,
    [RegisterYoutube]       NCHAR (500)     NOT NULL,
    [RegisterExternalLink]  NCHAR (500)     NULL,
    [RegisterNote]          NCHAR (60)      NULL,
    [RegisterNote2]         NCHAR (60)      NULL,
    CONSTRAINT [PK_Register] PRIMARY KEY CLUSTERED ([RegisterId] ASC),
    CONSTRAINT [FK_Register_Brand1] FOREIGN KEY ([RegisterBrandId1]) REFERENCES [dbo].[Brand] ([BrandId]),
    CONSTRAINT [FK_Register_Brand2] FOREIGN KEY ([RegisterBrandId2]) REFERENCES [dbo].[Brand] ([BrandId]),
    CONSTRAINT [FK_Register_Brand3] FOREIGN KEY ([RegisterBrandId3]) REFERENCES [dbo].[Brand] ([BrandId]),
    CONSTRAINT [FK_Register_Clothing1] FOREIGN KEY ([RegisterClothingId1]) REFERENCES [dbo].[Clothing] ([ClothingId]),
    CONSTRAINT [FK_Register_Clothing2] FOREIGN KEY ([RegisterClothingId2]) REFERENCES [dbo].[Clothing] ([ClothingId]),
    CONSTRAINT [FK_Register_Clothing3] FOREIGN KEY ([RegisterClothingId3]) REFERENCES [dbo].[Clothing] ([ClothingId])
);

另外两个是简单的表，只有 BrandId 和 BrandName，以及 Clothing Id 和 ClothingName

Answer 1

我认为您的寄存器表需要重新设计。名称为 [RegisterClothingNameX] 和 [RegisterClothingImgX] 的列应位于"服装"表上，除非它们没有描述特定的服装项目。下面的查询应该会给你你需要的数据

select 
Register.*, 
c1.ClothingName AS Clothing1Name, 
c2.ClothingName AS Clothing2Name, 
c3.ClothingName AS Clothing3Name, 
b1.BrandName AS Brand1Name,
b2.BrandName AS Brand2Name,
b3.BrandName AS Brand3Name
from 
Register
INNER JOIN Clothing c1 ON RegisterClothingId1 = c1.ClothingId
INNER JOIN Clothing c2 ON RegisterClothingId2 = c2.ClothingId
INNER JOIN Clothing c3 ON RegisterClothingId3 = c3.ClothingId
INNER JOIN Brand b1 ON RegisterBrandId1 = b1.BrandId
INNER JOIN Brand b2 ON RegisterBrandId2 = b2.BrandId
INNER JOIN Brand b3 ON RegisterBrandId3 = b3.BrandId

Answer 2

您的第一个联接将生成 3 行，所有这些 3 行都有 3 个品牌 ID（3 行 x 3 个品牌/列 - 所以第二个联接将产生 9 条记录）。这可能会根据存储在RegisterClothingId1，RegisterBrandId3中的值而变化