当联接列有不同的名称时，如何联接两个表?

I have these two tables and I need to create a report of PhraseId and English where the UserName is 'admin1'.

我有这两个表，我需要创建一个PhraseId和English的报告，其中用户名是“admin1”。

The ModifiedBy column matches up with the Id column.

修改的列与Id列相匹配。

CREATE TABLE [dbo].[Phrase] (
    [PhraseId]     UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [English]      NVARCHAR (250)   NOT NULL,
    [ModifiedBy]   INT              DEFAULT ((1)) NOT NULL,
    PRIMARY KEY CLUSTERED ([PhraseId] ASC),
    CONSTRAINT [FK_PhrasePhraseChapter] FOREIGN KEY ([ChapterId]) REFERENCES [dbo].[PhraseChapter] ([PhraseChapterShortId])
);

CREATE TABLE [dbo].[AspNetUsers] (
    [Id]                   INT            IDENTITY (1, 1) NOT NULL,
    [UserName]             NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);

How can I join these two tables when the join columns have different names?

当连接列有不同的名称时，如何连接这两个表?

3 个解决方案

#1

You need to specify the column names in the on clause:

您需要在on子句中指定列名:

SELECT p.[PhraseId], p.[English]
FROM   [dbo].[Phrase] p
JOIN   [dbo].[AspNetUsers] a ON p.[ModifiedBy] = a.[Id]
WHERE  a.[UserName] = 'admin'

#2

You can use any column names for joining (if it has sense). Usually, it is primary key for master table, and foreign key for detail table, wgich corresponds to master:

您可以使用任何列名连接(如果有意义的话)。通常是主表的主键，细节表的外键，wgich对应主键:

select * from
Table1 t1, Table2 t2
where t1.id=t2.ref_id

But which columns you wanna use for join these two tables?

但是你想用哪一列来连接这两个表呢?

#3

I would approach this by left joining the AspNetUsers to the Phrase table. This will allow all users to appear in the report, even if they have no matching phrase information. Note that COALESCE() is used to appropriately handle missing information for a given user, should that occur. In this case, we are targeting a specific user, but this approach will at least result in a single record showing up in the result set should the phrase information be missing.

我将通过将AspNetUsers加入短语表来实现这一点。这将允许所有用户出现在报告中，即使他们没有匹配的短语信息。注意，COALESCE()用于适当地处理给定用户丢失的信息(如果发生的话)。在本例中，我们针对的是一个特定的用户，但是这种方法至少会导致在结果集中出现一个记录，如果短语信息缺失的话。

SELECT t1.[Id],
       t1.[UserName],
       COALESCE(t2.[PhraseId], -1) AS PhraseId,
       COALESCE(t2.[English], 'NA') AS English
FROM [dbo].[AspNetUsers] t1
LEFT JOIN [dbo].[Phrase] t2
    ON t1.[Id] = t2.[ModifiedBy]
WHERE t1.[UserName] = 'admin1'

#1