72

I have three tables I wish to inner join by a common column between them.

Say my tables are;

TableA TableB TableC

I wish to join A-B, but then also B-C all by this common field I will call common.

I have joined two tables like this;

dbo.tableA AS A INNER JOIN dbo.TableB AS B
ON A.common = B.common

How do I add the third one?

William
  • 6,332
  • 8
  • 38
  • 57
  • 2
    dbo.tableA AS A INNER JOIN dbo.TableB AS B ON A.common = B.common inner join dbo.TableC as C on C.common=B.common – praveen Aug 06 '12 at 08:01

5 Answers5

135
select *
from
    tableA a
        inner join
    tableB b
        on a.common = b.common
        inner join 
    TableC c
        on b.common = c.common
LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • How would the output table would look like. Say SO questions and comments to each question, and answers to each question and comments to each answer. Three tables questions, answers, comments. – sçuçu Apr 19 '16 at 16:03
  • 1
    @ışık You would get all the columns from tableA, then all the columns from tableB, then all the columns from tableC. Or you can choose the columns you want – podiluska Apr 20 '16 at 13:19
  • 1
    What is happening behind the second INNER JOIN; are we joining on the output which asks Is there a temporary table getting created from the first join ? If there is a temporary table then how are we calling that table. – lft93ryt Sep 03 '17 at 11:13
  • @lft93ryt that depends on the tables, and how they are optimised. You can't refer to the "temporary table". – podiluska Sep 04 '17 at 08:26
17

Just do the same thing agin but then for TableC

SELECT *
FROM dbo.tableA A 
INNER JOIN dbo.TableB B ON A.common = B.common
INNER JOIN dbo.TableC C ON A.common = C.common
Bazzz
  • 26,427
  • 12
  • 52
  • 69
8
dbo.tableA AS A INNER JOIN dbo.TableB AS B
ON A.common = B.common INNER JOIN TableC C
ON B.common = C.common
Arkiliknam
  • 1,805
  • 1
  • 19
  • 35
7

try the following code

select * from TableA A 
inner join TableB B on A.Column=B.Column 
inner join TableC C on A.Column=C.Column
Ram Singh
  • 6,664
  • 35
  • 100
  • 166
3

try this:

SELECT * FROM TableA
JOIN TableB ON TableA.primary_key = TableB.foreign_key 
JOIN TableB ON TableB.foreign_key = TableC.foreign_key
wilx
  • 17,697
  • 6
  • 59
  • 114
Omar Faruk
  • 389
  • 2
  • 8