SQL:如何基于另一个表中的相同字段创建两个字段? [英] SQL: How to create two fields based on same field from another table?

问题描述

我确信这很容易找到，但是通过搜索，我一直找不到解决方案.我可能没有在寻找正确的关键字，因此希望有人可以帮助我.

I am sure this is quite easy to find, but through my searching I have been unable to find a solution. I'm probably not looking for the right keywords, so hopefully someone can help me.

我有两个表，并且我试图运行一个选择查询，该查询将基于另一个表中的相同字段创建两个新字段.

I have two tables and I am trying to run a select query that will create two new fields based on the same field from another table.

示例: Table1包含两个名为AgencyCountryCode和ClientCountryCode的字段，因此我需要创建两个名为AgencyCountryName和ClientCountryName的新字段.在Table2中，我列出了要匹配的国家代码和国家名称.

Example: Table1 contains two fields called AgencyCountryCode and ClientCountryCode so I need to create two new fields called AgencyCountryName and ClientCountryName. In Table2 I have a list of country codes and country names to match with.

我已使用以下代码将代理商国家/地区代码与名称匹配，但我无法弄清楚如何使用相同的查找表/字段为客户执行相同的操作.

I have used the below code to match the agency country code to the name, but I can't figure out how to do the same thing for client, using the same lookup table/field.

SELECT
...various other fields...
CountryList.[CountryName] AS AgencyCountryName, 
FROM BookingData 
INNER JOIN CountryList ON BookingData.[Agency Country Code] = CountryList.[CountryCode];

任何人都可以让我知道如何在两个不同的字段上执行相同的查找吗?

Can anyone please let me know how I can perform the same lookup on two different fields?

非常感谢.

推荐答案

您只需要添加另一个JOIN:

SELECT     ...various other fields...
           ACL.[CountryName] AS AgencyCountryName, 
           CCL.[CountryName] AS ClientCountryName
FROM       BookingData BD
INNER JOIN CountryList ACL ON BD.[Agency Country Code] = ACL.[CountryCode]
INNER JOIN CountryList CCL ON BD.[Client Country Code] = CCL.[CountryCode]

这篇关于SQL:如何基于另一个表中的相同字段创建两个字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！