How i to get the exact match join for the below scenario?

Question

How do i join the below tables

  TableA    TableB             TableC           TableD
   ID ID_C  ID  ID_A Value       ID            ID  ID_C Value
    1  1     1   1    a          1              1   1    a
             2   1    b                         2   1    b

in order to get the Result like

  Result
  ID ID_B Value  ID_C ID_D Value
  1   1     a      1     1  a
  1   2     b      1     2  b

and my result shouldn't contain 1 2 b 1 1 b and both value columns cannot always have same values so it cannot be used in a condition.

To make it simplier,

   Resultant Table           TableA              TableB
   ID   Value                ID Value            ID  ID_A
    1   a                     1  a               1    1        
    1   b                     2  g               2    1
    2   a                     3  d               3    2
    3   c                                        4    3

Now i need to join the Resultant Table with TableA,TableB inorder to get some of the columns from TableA,TableB and ResultantTable.ID=TableA.ID and TableB.ID_A=TableA.ID since its a foreign key.

Doing the Join with TableB turns to duplicates. Since ID=1 occurs twice i get 4 records where ID=1, when there are only 2 records. It can be done with distinct or group by but i need other columns as well to be displayed.How do i do both in the process.

Answer 1

SELECT A.ID, B.ID, B.Value, C.ID, D.ID, D.Value
FROM TableA A
INNER JOIN TableB B ON A.ID = B.ID_A
INNER JOIN TableC C ON A.ID_C = C.ID
INNER JOIN TableD D ON B.ID = D.ID AND C.ID = D.ID_C

Answer 2

You tell us that the field "value" in TableB should not be different from the field "value" in TableD? Could we replace the B.ID = D.ID with B.Value = D.Value so solve your problem?

Are you sure, that is the way that is suppose to work?

Answer 3

Try:

SELECT A.ID, B.ID ID_B, B.Value Value_B, C.ID ID_C, D.ID ID_D, D.Value Value_D
FROM TableA A
JOIN TableB B ON A.ID = B.ID_A
JOIN TableC C ON A.ID_C = C.ID
JOIN TableD D ON B.Value = D.Value AND C.ID = D.ID_C