具有多个查询的SQL查询 [英] SQL Query with multiple lookups

问题描述

我遇到了一个表，其中包含五个引用客户列表的字段.字段(c1，c2等)是另一个表中客户的ID.需要一条SQL语句，它将客户名称解析为一个包含在线和五个整数的报表.示例:

I have encountered a table with five fields refering to a customer list. The fields (c1, c2, etc..) are id's of the customer in the other table. Need a SQL statement that will resolve the customer names into a report with on line and the five entires. Example:

Table1
ID  Description   C1   C2   C3
1   PartyGroup     2    3    1
2   BeerFolk       1    0    2

Customer table
ID  Name
1   Rob
2   Joe
3   Fred


Output:
ID  Description  Cust1 Cust2  Cust3
1   PartyGroup   Joe   Fred   Rob
2   BeerFolk     Rob          Joe

任何想法将不胜感激...

any thought would be appreciated...

推荐答案

SELECT t1.ID, t1.Description, c1.Name AS Cust1, c2.Name AS Cust2, c3.Name AS Cust3
    FROM Table1 t1
        LEFT JOIN Customer c1
            ON t1.C1 = c1.ID
        LEFT JOIN Customer c2
            ON t1.C2 = c2.ID
        LEFT JOIN Customer c3
            ON t1.C3 = c3.ID

这篇关于具有多个查询的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！