joining of 2 tables without repeating.

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

Transact-SQL (2000)

joining of 2 tables without repeating.

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2003-06-27 : 14:04:13

I’m new to SQL and I have been looking around for help on how to acomplish the results that I need.
I am trying to join 2 tables.
My goal: Have 1 table with the item number not repeating because of different values in ‘House/Mohtq’.

Table1 ‘ITEMASA’

ITNBR | ITDSC
00001 | d250 housing
00002 | d250 cable

Table2 ‘ITEMBL’

ITNBR | HOUSE | MOHTQ
00001 | 2 | 300
00001 | 3 | 20
00001 | M | 5
00002 | 2 | 0
00002 | 3 | 40
00002 | M | 50
etc etc

The way I would like for it to look is like this:

ITNBR | ITDSC | HOUSE2 | HOUSE3 | HOUSEM
00001 | d250 housing | 300 | 20 | 5
00002 | d250 cable | 0 | 40 | 50

Any help would be greatly apreciated. Thanks in adv.

SQL Server 2000

Edited by - jose1lm on 06/27/2003 14:09:37

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-27 : 15:33:52

select ITNBR ,
ITDSC ,
HOUSE2 = (select MOHTQ from ITEMBL b where b.ITNBR = a.ITNBR and b.HOUSE = '2') ,
HOUSE3 = (select MOHTQ from ITEMBL b where b.ITNBR = a.ITNBR and b.HOUSE = '3') ,
HOUSEM = (select MOHTQ from ITEMBL b where b.ITNBR = a.ITNBR and b.HOUSE = 'M')
from ITEMSA a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2003-06-30 : 16:53:59

Thx nr, that worked great!

JLM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources