Inner join 3 tables returns nothing

Question

have 3 tables.

CB table

      CID   EC ID   Status  CType
      1001  1001    Active  1540
      1004  1001    Active  4

Table M

CID     EID        EC ID    EID-CID
1001      1166          1001    1166_1001
1001      1167a         1001    1167a_1001
1001      1167b         1001    1167b_1001
1001      1168          1001    1168_1001
2014.2071 1043          2018    1043_2014.2071
1004      1166          1001    1166_1004
1004      1167a         1001    1167a_1004
1004      1168          1001    1168_1004
1004      1167b         1001    1167b_1004

Third table(CM)

EID           CID            EID-CID      EC ID          CType
1043    2014.2071     1043_2014.2071      2018            4

I'm trying to inner join 3 tables. And i'm using query below(i refered stackoverflow page Inner Joining three tables) My query is returns nothing. but not giving any error messages.

SELECT 
  M.EID, 
  CB.CID, 
  M.[EID-CID], 
  CB.[CType],
  CB.[EC ID] 
FROM 
  (CB INNER JOIN M ON CB.CID = M.CID) 
  inner join CM on CM.CID=M.CID

Result should be like this

 CID        EID       EC ID EID-CID      CType
1001        1166    1001    1166_1001       1540
1001        1167a   1001    1167a_1001      1540
1001        1167b   1001    1167b_1001      1540
1001        1168    1001    1168_1001       1540
2014.2071    1043   2018    1043_2014.2071  5.5
1004        1166    1001    1166_1004        4
1004        1167a   1001    1167a_1004       4
1004        1168    1001    1168_1004        4
1004        1167b   1001    1167b_1004       4

Answer 1

When using inner join only results that have a match on the join criteria would be returned.

1. CB table has two IDs: 1001, 1004
2. M table has both of those IDs (would be returned at this point of the query)
3. CM table has none of the IDs from CB.

Doing an inner join between the three tables produces zero results as it should.

If you remove the join on CM you'll have results related to the two IDs from CB, or if you were to join only on the M and CM tables you would have your 2014.2071 row, however there are no records that exist throughout all three tables. Due to using inner joins between all three tables, you are receiving the results you should (based on what you have written), which is zero records.

Answer 2

Inner join only returns results where there is a match. It looks like you have join types messed up. Based on how you say the results should look, I assume you are looking for a LEFT or RIGHT JOIN. e.g.

SELECT 
M.EID, CB.CID, M.[EID-CID], CB.[CType],CB.[EC ID] 
FROM 
  (CB RIGHT JOIN M ON CB.CID = M.CID) LEFT JOIN CM  on CM.CID=M.CID

You might want to take a look at this website: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Answer 3

Your join to CM is joining on the wrong columns: you are joking on CID, but you should be joining on EID:

SELECT M.EID, CB.CID, M.[EID-CID], CB.[CType],CB.[EC ID]
FROM CB
JOIN M ON CB.CID = M.CID
JOIN CM ON CM.EID = M.EID -- change here

Removed unnecessary brackets.