CODEWITHSUNDEEP
sqlsql-server
James
James

Reputation: 1945

Getting data from second table

I have 2 tables.

Table1 has columns like

ID int
FromAddress int
ToAddress int

Table2 has columns like

ID int
ZipCode int

The Connection between 2 tables is based on FromAddress,ToAdress(from Table1) and ID in table2

Rough data is like in Table1

ID     FROMAddress    ToAddress
1      500            620

Roughdata is like in Table2

ID     ZipCode
500    69999
620    6501

I want output like

ID     FromZipCode     ToZipCode
1      69999           6501

I tried to do Query like

Select T1.ID, T2.ZipCode as FromZipCode, T2.ZipCode as ToZipCode
From Table1 T1 join Table2 T2 on T1.FromAddress = T1.ID and T1.ToAddress = T2.ID

But this gives me no result.

Upvotes: 0

Views: 44

Answers (2)

Ilyes
Ilyes

Reputation: 14928

You can join the table twice to get your desired data:

SELECT T1.ID,
       T2.ZipCode AS FromZipCode,
       TT2.ZipCode AS ToZipCode --From the second Table2 (TT2)
From Table1 T1 JOIN Table2 T2 ON T1.FromAddress = T1.ID 
     JOIN Table2 TT2 ON T1.ToAddress = TT2.ID --Join Table2 for the second time with another alias

Upvotes: 0

Valerica
Valerica

Reputation: 1630

Try like this, you need to JOIN twice:

Select T1.ID
    ,T2.ZipCode as FromZipCode
    ,T22.ZipCode as ToZipCode
From Table1 T1
join Table2 T2
    on T1.FromAddress = T2.ID -- first JOIN to get the info for FromAddress
join Table2 T22 
    on T1.ToAddress = T22.ID -- second JOIN to get the info for ToAddress

Upvotes: 1

Related Questions