SQL Server Join具有相同列名的不同表

I have 3 database tables, table A, table B, Table C. Table B And Table C are different name and have same columns name, and table C have two columns more from table b and table C rows of data can be more than table B.

我有3个数据库表，表A，表B，表C.表B和表C是不同的名称并且具有相同的列名称，而表C有两列多来自表b和表C行的数据可以多于表B.

Table A
-----------------------------------
ContractNo  | Contact
-----------------------------------
001         | AAA

Table B
---------------------------
ID  | ContractNo | Column A 
---------------------------
1   | 001        | Info A1
2   | 001        | Info A2

Table C 
---------------------------------------
ID  | ContractNo | Column A | Column B 
---------------------------------------
6   | 001        | Info A1   | Info AA1
7   | 001        | Info A2   | Info AA2
8   | 001        | Info A3   | Info AA3

When I query:

当我查询时：

SELECT * FROM tableA as A 
INNER JOIN tableB AS B ON A.ContractNo = B.ContractNo 
INNER JOIN tableC AS C ON A.ContractNo = C.ContractNo
WHERE A.ContractNo = '001'

The Result:

结果：

-----------------------------------------------------------------------------
ContractNo  | Contact | ID | Column A | ID | ContractNo | Column A | Column B
-----------------------------------------------------------------------------
001         | AAA     | 1  | Info A1  | 6  | 001        | Info A1  | Info AA1
001         | AAA     | 1  | Info A1  | 7  | 001        | Info A2  | Info AA2
001         | AAA     | 1  | Info A1  | 8  | 001        | Info A3  | Info AA3
001         | AAA     | 2  | Info A2  | 6  | 001        | Info A1  | Info AA1
001         | AAA     | 2  | Info A2  | 7  | 001        | Info A2  | Info AA2
001         | AAA     | 2  | Info A2  | 8  | 001        | Info A3  | Info AA3

Expected Result:

ContractNo  | Contact | ID    | Column A | ID | ContractNo | Column A | Column B
-----------------------------------------------------------------------------
001         | AAA     | 1     | Info A1  | 6  | 001        | Info A1  | Info AA1
001         | AAA     | 2     | Info A2  | 7  | 001        | Info A2  | Info AA2
001         | AAA     | NULL  | NULL     | 8  | 001        | Info A3  | Info AA3

And if there are 3 rows data in table B with ContractNo = '001' and 4 rows in table C with ContractNo = '001', I got 12 rows as result. Expected result 4 rows.

如果表B中有3行数据，ContractNo ='001'，表C中有4行，ContractNo ='001'，我得到12行结果。预期结果4行。

2 个解决方案

#1

Thats because you may want to try Coalesce ...

那是因为你可能想尝试Coalesce ......

here is a snippet

这是一个片段

SELECT COALESCE(TableA.Id,TableB.Id)'Id'
      , TableA.ContractNo  '32'
      , TableA.zone 'zone-2012'
      , TableA.inspect 'inspect-2012'
      , TableB.ContractNo  'xx'
      , TableB.zone 'zone-2009'
      , TableB.inspect 'inspect-2009'
INTO NewTable
FROM TableC a
FULL JOIN TableC b
   ON TableA.Id = TableB.I

You should be able to adapt it get the result you want!

你应该能够适应它，得到你想要的结果！

#2

Based on your sample data

根据您的样本数据

SELECT A.contractno,
       A.contractno,
       B.id,
       B.[column a],
       C.id,
       C.contractno,
       C.[column a],
       C.[column b]
  FROM [Table A] A
  LEFT OUTER JOIN [Table C] C
    ON A.ContractNo = C.ContractNo
  LEFT OUTER JOIN [Table B] B
    ON B.ContractNo = C.ContractNo
   AND B.[column a] = C.[column a]

Result

结果

contractno  contact id      column a    id  contractno  column a    column b
001         AAA     1       Info A1     6   001         Info A1     Info AA1
001         AAA     2       Info A2     7   001         Info A2     Info AA2
001         AAA     NULL    NULL        8   001         Info A3     Info AA3

#1