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

The only CID in the CM table has a value of 2014.2071, that value does match a record from the M table but not from the CB table hance you get no result. — Luc, Jun 22 '15 at 18:07
If you're expecting results to pull back from this query, you might be looking for a `Left join` instead of an inner join, in one if not both of the tables you're joining against. This will pull back values for the tables you're joining against, or if there's no values to pull back, it will pull back null values instead. Not certain if this is what you're looking for, but it seems like it might be the case. Link for reference: http://www.w3schools.com/sql/sql_join.asp — user2366842, Jun 22 '15 at 18:30
You aren't actually selecting anything from table CM, is your join on that table even necessary? Your schema/problem is a little hard to understand as there seems to be a lot of overlap between your tables, their columns, and their data, without much explanation as to why. — Kritner, Jun 22 '15 at 18:49
@user254948 It return all rows but CType column only has 5.5 data. — sam, Jun 22 '15 at 18:52

Kritner · Answer 1 · 2015-06-22T18:25:17.127

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

CB table has two IDs: 1001, 1004
M table has both of those IDs (would be returned at this point of the query)
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.

score 0 · Answer 2 · edited Jun 22 '15 at 18:11

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/

score 0 · Answer 3 · answered Jun 22 '15 at 18:10

0

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.

answered Jun 22 '15 at 18:10

Bohemian

412,405
93
575
722

"wrong column" might not be accurate; kind of hard to tell based on the schemas given and all of the abbreviations though :O Either way it still looks like changing the join column would produce zero results, as the `2014.2071` row would never be returned based on the join and data between CB and M – Kritner Jun 22 '15 at 18:21

Inner join 3 tables returns nothing

3 Answers3