Multiple inner join on same table

Question

I have three tables : Table a

UID
SellID
BuyID
SAMonth

Table B

ID
ZipCode

Table C

Zip Code
Latitude 
Longitude

Now i have to find distance between two ID's

Firstly i was having UID and based on that UID i tried to get two Zip code Corresponding to each ID using following code.

Select t1.UID,t1.SellID,z1.ZipCode, t1.BuyID, z2.ZipCode , SAMonth 
from tableA t1
inner join tableB z1 on t1.SellID Like z1.ID 
inner join tableB z2 on t1.BuyID Like z2.ID 
where Criteria

what is wrong in this code, i am getting Syntax error in this when i am adding second inner join. Also how to use this Zip to get latitude and longitude for both ID's

It is working with Like Keyword because of the difference of Format. After adding brackets its working. How Can it be extended to 3rd table

Build the query in the GUI designer, and you'll see that is will replace _Like_ and set two sets of parenthesis. — Gustav, Aug 26 '16 at 09:52
Possible duplicate of [SQL Inner Join wih multiple table](http://stackoverflow.com/questions/19367565/sql-inner-join-wih-multiple-table) — Andre, Aug 26 '16 at 09:52

onedaywhen · Answer 1 · 2016-08-26T10:50:16.690

You need to put one of the joins into parens e.g.

Select t1.UID,t1.SellID,z1.ZipCode, t1.BuyID, z2.ZipCode , SAMonth 
  from ( tableA t1
         inner join tableB z1 on t1.SellID Like z1.ID )
         inner join tableB z2 on t1.BuyID Like z2.ID 
 where Criteria

The order this forces you to write is simply for parsing purposes, the optimizer will use whichever order it determines to be best.

As others have pointed out, your data element names (ID) suggest you should replace like with = (equals).

Adding a third table involves adding a further level of parens nesting e.g.

from ( ( tableA t1
         inner join tableB z1 on t1.SellID Like z1.ID )
         inner join tableB z2 on t1.BuyID Like z2.ID )
         inner join tableC z3 on z1.ZipCode = z3.ZipCode

score 0 · Answer 2 · answered Aug 26 '16 at 09:53

0

Replace that like with = equality comparison

inner join tableB z1 on t1.SellID = z1.ID 
inner join tableB z2 on t1.BuyID = z2.ID

answered Aug 26 '16 at 09:53

Rahul

76,197
13
71
125

Ruwaid Bin Yousuf · Answer 3 · 2016-08-26T10:10:11.530

0

Select t1.UID,t1.SellID,z1.ZipCode, t1.BuyID, z2.ZipCode , t1.SAMonth 
from tableA t1
inner join tableB z1 on t1.SellID = z1.ID 
inner join tableB z2 on t1.BuyID = z2.ID

edited Aug 26 '16 at 10:10

answered Aug 26 '16 at 10:01

Ruwaid Bin Yousuf

1
4

Multiple inner join on same table

3 Answers3