问题描述
我需要从表应用程序(左)和表服务(右)中选择中间表中的值的名称。因为中间表包含表应用程序和服务的所有外键。我知道我应该使用连接或东西。 HELP!
表格定义:
表1:应用程序
列1:int ApplicationID(PK)
列2:nvarchar )名称
表2:服务
列1:int ServiceID(PK)
列2:nvarchar(255)名称
映射表:ApplicationToService
列1:int ApplicationToServiceID(PK)
列2 :int ApplicationID(FK)
列3:int ServiceID(FK)
你确实需要一个JOIN命令。
在下面的示例中,我使用INNER JOIN命令作为最常用的(至少对我来说),但如果您愿意,可以用另一个联接替换。 / p>
下面是您可以使用的不同类型的联接图:
根据您的表格是:
表1:应用程序
- 第1列:int ApplicationID PK)
- 第2列:nvarchar(255)名称
表2:服务(PK)
- / li>
- 第2列:nvarchar(255)名称
映射表 ApplicationToService (PK) ApplicationToService
-
- 第2列:int ApplicationID (FK)
- 第3列: li>
示例:
SELECT
*
FROM
ApplicationToService
INNER JOIN应用程序ON ApplicationToService.ApplicationID = Application.ApplicationID
INNER JOIN服务ON ApplicationToService.ServiceID = Service.ServiceID
您可以用单独的字段替换*,但由于您现在处理多个表,因此必须在每个列名前加上表名称如下:
SELECT
ApplicationToService.ApplicationToServiceID
,Application.Name
.Name
FROM
ApplicationToService
INNER JOIN Application ON ApplicationToService.ApplicationID = Application.ApplicationID
INNER JOIN服务ON ApplicationToService.ServiceID = Service.ServiceID
您的表和列名称可能比我的更简洁,但我发现保留它们描述性的良好做法。
如果您需要进一步解释,请不要害怕。
I need to select from table application(left) and table services(right) the name of the values in the middle table. since the middle table contains all foreign keys of the table application and services. i know im supposed to use join or something. HELP!
Table definitions:
Table 1: Application
Column 1: int ApplicationID (PK)
Column 2: nvarchar(255) Name
Table 2: Service
Column 1: int ServiceID (PK)
Column 2: nvarchar(255) Name
Mapping table: ApplicationToService
Column 1: int ApplicationToServiceID (PK)
Column 2: int ApplicationID (FK)
Column 3: int ServiceID (FK)
You will indeed require a JOIN command.
In the following example I use the INNER JOIN command as it's the most commonly used (at least for me), but you may replace with another join if you prefer.
Here is a diagram of the different types of joins at your disposal:
Based on the assumption that your tables are:
Table 1: Application
- Column 1: int ApplicationID (PK)
- Column 2: nvarchar(255) Name
Table 2: Service
- Column 1: int ServiceID (PK)
- Column 2: nvarchar(255) Name
Mapping table: ApplicationToService
- Column 1: int ApplicationToServiceID (PK)
- Column 2: int ApplicationID (FK)
- Column 3: int ServiceID (FK)
Example:
SELECT
*
FROM
ApplicationToService
INNER JOIN Application ON ApplicationToService.ApplicationID = Application.ApplicationID
INNER JOIN Service ON ApplicationToService.ServiceID = Service.ServiceID
You may replace the * with individual fields, but since you are now dealing with multiple tables, you must precede each column name with the table name like so:
SELECT
ApplicationToService.ApplicationToServiceID
, Application.Name
, Service.Name
FROM
ApplicationToService
INNER JOIN Application ON ApplicationToService.ApplicationID = Application.ApplicationID
INNER JOIN Service ON ApplicationToService.ServiceID = Service.ServiceID
Your table and column names may be less verbose than mine, but I find it good practice to keep them descriptive.
If you need this explaining further then don't be afraid to ask.
这篇关于SQL使用外键从三个表中选择多个的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!