如何基于同一列显示Code_Master表中的多个代码

I have a Code_Master table which has 3000+ codes. These codes are embedded in all of my tables, but as different column names...

我有一个Code_Master表,有3000多个代码。这些代码嵌入在我的所有表中,但作为不同的列名...

Example: I have a table called Notes which houses incoming call notes for all student accounts (All Student Acct info is held in table: CA_Prof). I have 3 columns in this table which display codes from the Code_Master table. I have a Note_Category column (Codes 1-5), a Caller_Mood column (Codes 6-10) and a Caller column (Codes 11-15).

示例:我有一个名为Notes的表,其中包含所有学生帐户的来电通知(所有学生帐户信息保存在表格中:CA_Prof)。我在此表中有3列显示Code_Master表中的代码。我有一个Note_Category列(代码1-5),一个Caller_Mood列(代码6-10)和一个Caller列(代码11-15)。

In the CA_Prof table I have a column named C_ID (Student Acct #) and CA_Id (Sub Account (Is this their first, second, third acct, etc). I also have a column S_Id (School ID# for the school they attend)

在CA_Prof表中,我有一个名为C_ID(学生帐号#)和CA_Id(子帐户(这是他们的第一个,第二个,第三个帐号等)的列。我还有一个列S_Id(他们参加的学校的学校ID#)

I want to be able to display:

我希望能够显示:

Note_Category, Code_Description (from Code_Master), Caller_Mood, Code_Description (from Code_Master), Caller, Code_Description (from Code_Master)

Note_Category,Code_Description(来自Code_Master),Caller_Mood,Code_Description(来自Code_Master),来电者,Code_Description(来自Code_Master)

but only for records that the C_ID and CA_ID found in Notes records match the C_Id and CA_Id from CA_Prof

但仅适用于在Notes记录中找到的C_ID和CA_ID与CA_Prof中的C_Id和CA_Id匹配的记录

I can't figure how to do this with the different columns using codes from the same Code_Master table.

我无法使用相同Code_Master表中的代码来计算如何使用不同的列执行此操作。

Here is what I have:

这是我有的:

    SELECT
    can.C_Id,
    can.CA_Id,
    can.Note_No,
    can.Caller_Mood,
    cm.Code_Desc,
    can.Caller,
    can.Note_Category,
    can.How_Contacted_Code,
    can.Note,
    can.Insert_Time,
    can.Insert_User
FROM Notes can (NOLOCK)
INNER JOIN CA_Prof cap
    ON can.C_Id = cap.C_Id
    AND can.CA_Id = cap.CA_Id
Inner Join Code_Mstr cm on can.Caller_Mood=cm.Code_Id 
WHERE cap.S_Id = 8027
    AND can.Insert_Time >= '05/01/2014'
    AND can.Insert_Time <= '01/01/2015'
    AND can.How_Contacted_Code = '331'
ORDER BY can.Note_Cat_Code ASC;

1 个解决方案

#1

You can join to the same table more than once. I think you want something like this:

您可以多次加入同一个表。我想你想要这样的东西:

select
    ...
    n.Note_Category
    nc.Code_Description as Note_Category_Description,
    ...
    n.Caller_Mood,
    cm.Code_Description as Caller_Mood_Description,
    ...
from Notes n
join Code_Master nc on nc.Code_Id = n.Note_Category
join Code_Master cm on cm.Code_Id = n.Caller_Mood
...