Using Two Columns in SQL INNER JOIN

Question

TableA

AID     AName
1       Alpha
2       Bravo
3       Charlie

TableB

BID     BName
1       Delta
2       Echo
3       Foxtrot

TableC

CID     AID     BID
1       1       null
2       null    2
3       3       null

I am using the following SQL statement:

SELECT C.CID, A.Name AS First, B.Name AS Second FROM TableC 
INNER JOIN TableA ON TableC.AID = TableA.AID
INNER JOIN TableB ON TableC.BID = TableB.BID

but displays a blank record. The expected result should be:

CID     First     Second
1       Alpha
2                 Echo
3       Charlie

Any suggestions?

score 3 · Accepted Answer · answered Nov 21 '13 at 11:51

3

Use LEFT JOIN instead of INNER JOIN.

answered Nov 21 '13 at 11:51

GriGrim

2,742
1
17
31

Ohh. It was a rookie mistake. Thank you! – abramlimpin Nov 21 '13 at 11:55

Andre Figueiredo · Answer 2 · 2021-05-24T02:21:57.200

1

Use LEFT JOIN for each A, B table

SELECT C.CID, A.AName, B.BName
FROM C
LEFT JOIN A ON C.AID = A.AID
LEFT JOIN B ON C.BID = B.BID

edited May 24 '21 at 02:21

answered Nov 21 '13 at 11:53

Andre Figueiredo

11,944
7
45
70

score 1 · Answer 3 · answered Nov 26 '13 at 07:26

1

SELECT C.CID, A.Name AS First, B.Name AS Second FROM TableC LEFT JOIN TableA ON TableC.AID = TableA.AID LEFT JOIN TableB ON TableC.BID = TableB.BID

answered Nov 26 '13 at 07:26

Swathi

84
1

Using Two Columns in SQL INNER JOIN

3 Answers3