SP Logic - 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 2000 Forums

SQL Server Development (2000)

SP Logic

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2002-11-27 : 13:24:20

I have 7 tables with these structures:

tableA
lngIndex
txtCode1
txtCode2
txtCode3
txtCode4
txtCode5
txtCode6

table1
txtCode
txtDescription

table2
txtCode
txtDescription

table3
txtCode
txtDescription

and so on. txtCode1 gets its description from table1, txtcode2 from table2, etc. What I want to do is do a "select all fields from tableA, corresponding descriptions from tables[1-6] for a particular lngIndex". I don't know what to do. I can't restructure the table structures. Pls help.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-27 : 13:29:52

Just a long join statement

select ingindex, b.txtDescription,c.txtDescription,d.txtDescription, e.txtDescription, f.txtDescription,g.txtDescription
from tablea a inner join tableb b on a.textcode1 = b.textcode
inner join tablec c on a.textcode2 = c.textcode
--and so on for each table
where ingcode = 'which code ya wanna look up?'

Hmm, just noticed it's table1 not table b... oh well hopefull you understand my point. Depending on your data you may want to use left joins instead of inner joins

Hey, Post 500... I feel special now.

-----------------------
SQL isn't just a hobby, It's an addiction

Edited by - M.E. on 11/27/2002 13:30:14

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-11-27 : 13:30:06

try something like...


select ta.IngIndex, t1.*, t2.*, ...
from tableA ta left join table1 t1
  on ta.txtCode1 = t1.txtCode
left join table2 t2
  on ta.txtCode2 = t2.txtCode
... repeat for the other 4 tables
where IngCode like 'foo'

see joins, specifically left and right, in Books On Line for more info.

hth,
Justin

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-11-27 : 13:31:49

Damn sniped!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-27 : 13:33:33

[code]
SELECT
lngIndex,
txtCode1,
table1.txtDescription as Description1,
txtCode2,
table2.txtDescription as Description2,
...
txtCode7,
table7.txtDescription as Description7
FROM
tableA
LEFT OUTER JOIN table1 ON tableA.txtCode1 = table1.txtCode
LEFT OUTER JOIN table2 ON tableA.txtCode2 = table2.txtCode
...
LEFT OUTER JOIN table7 ON tableA.txtCode7 = table7.txtCode

If there is always a match in the text tables, you can replace the LEFT OUTER JOINS with INNER JOINS.

- Jeff

Edited by - jsmith8858 on 11/27/2002 13:36:34

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-27 : 13:34:25

me too! you guys are quick!

- Jeff

Edited by - jsmith8858 on 11/27/2002 13:34:40

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-27 : 13:39:36

Heh, my 500th post has sniped two peeps... Talk about a great