Inner Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inner Joins

Author

Topic

Jabez
Starting Member

19 Posts

Posted - 2007-01-26 : 08:32:36

Hi Folks,

Please help me out in forming this query.

Table1 : Address
Table2 : AddressDetail

Sample Data for Table1

A_ID A_Desc
---- ------
1 Asia
2 India
3 Karnataka
4 Bangalore
5 Andhra
6 Hyderabad

Sample Data for Table2

AD_ID A_ID1 A_ID2 A_ID3 A_ID4
----- ----- ----- ----- -----
1 1 2 3 4
2 1 2 5 6

How do I write query using these two tables to get the data in the below format

Asia -- India -- Karnataka -- Bangalore
Asia -- India -- Andhra -- Hyderabad

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-26 : 08:54:54

You just need to join to Table1 from Table2 multiple times, once for each column. Just give each "copy" of Table1 a different alias. i.e.,

select t.a_id, t2.a_desc, t3.a_desc, t4.a_desc, t5.a_desc
from table1 t
inner join table2 t2 on t.a_id1 = t2.a_id
inner join table2 t3 on t.a_id2 = t2.a_id
inner join table2 t4 on t.a_id3 = t2.a_id
inner join table2 t5 on t.a_id4 = t2.a_id

By the way -- you should try to normalize these tables if possible, they are very poorly designed.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-26 : 08:55:10

[code]SELECT t11.A_Desc,
t12.A_Desc,
t13.A_Desc,
t14.A_Desc
FROM Table2 AS t2
LEFT JOIN Table1 AS t11 ON t11.A_ID = t1.A_ID1
LEFT JOIN Table1 AS t12 ON t12.A_ID = t1.A_ID2
LEFT JOIN Table1 AS t13 ON t13.A_ID = t1.A_ID3
LEFT JOIN Table1 AS t14 ON t14.A_ID = t1.A_ID4
ORDER BY t2.AD_ID[/code]

Peter Larsson
Helsingborg, Sweden

Jabez
Starting Member

19 Posts

Posted - 2007-01-31 : 03:08:17

Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources