SQL Server Join Different table with same columns name

Question

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.

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.

The problem is the join. You join from a to b and get 2 rows. Then from table a to c you get 3 rows. Two parent to child joins from the same parent yields a multiplicative effect with Sql. It takes the 2 rows and multiplies it by the 3 rows. This is frequently called a Cartesian join. Change the field names around and you will have the same problem. — M T Head, Aug 27 '17 at 00:59
You don't explain what rows you want output in terms of input; you just give some *wrong* code and one example of what you want, plus partial info re another. Please read & act on [mcve]. Fill in the missing parts of this: Row (ContractNo_1,Contact,ID_1,Column_A_1,ID_2,...) is in the result when a row (A.ContractNo,...) is in tableA and ... and A.ContractNo = B.ContractNo and ... or ... and A.ContractNo <> B.ContractNo and ID_1 IS NULL and Column_A_1 IS NULL .... [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) — philipxy, Aug 27 '17 at 02:47

score 0 · Answer 1 · answered Aug 26 '17 at 22:45

0

Thats because you may want to try 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!

answered Aug 26 '17 at 22:45

Transformer

6,963
2
26
52

If I have 2 rows in tablesB with same ContractNo and 3 rows in tablesC, the results are 6 rows. What I want are 3 rows. – Blishton Aug 26 '17 at 23:12

score 0 · Answer 2 · answered Aug 27 '17 at 22:14

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

SQL Server Join Different table with same columns name

Expected Result:

2 Answers2