linking tables in the query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

linking tables in the query

Author

Topic

nbs
Starting Member

22 Posts

Posted - 2007-12-06 : 22:13:54

Hi all... I am trying to get data from three tables. The first two have a field that I can link on to. The 2nd and 3rd have a different field that I can link on to.

Table 1

ID1 class
12 a
13 z
14 p
15 s
16 v

Table 2
ID1 ID2 category
12 34 G2
13 35 H4
14 78 A3
14 96 F4
15 71 G3
16 88 J1
16 89 K1

Table 3
ID2 Currency
34 Rs
35 Usd
71 Ukp
78 Lir
88 Mex
89 Can
96 Rr

I need to pick ID1 from table 1 and table 2 and then pick the associated ID2 and link it to table 3 to get the currency.

I tried...

Select table1.class, table2.category, table3.currency
from table1, table2, table3
where table1.ID1 = table2.ID1 and table2.ID2 = table3.ID2

but it doesnt work right....

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-06 : 23:03:53

Do you mean this?

Select table1.class, table2.category, table3.currency
from table1, table2, table3
where table1.ID1 = table2.ID1 and table2.ID2 = table3.ID2

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-06 : 23:08:03

ansi join syntax is preferred:

Select table1.class, table2.category, table3.currency
from table1
join table2 on table1.ID1 = table2.ID1
join table3 on table2.ID2 = table3.ID2

your query is probably broken because of this: "table2.ID2 = table2.ID3"

there is no ID3 column in table2. furthermore you are not referring to table3 anywhere in your where clause.

elsasoft.org

nbs
Starting Member

22 Posts

Posted - 2007-12-06 : 23:12:34

My bad... Its table2.ID2= table3.ID2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources