DTS and SQL2000 - for producing reports

[RogueSuit]

DTS and SQL2000 - for producing reports

I am using SQL Enterprise Manager to create a DTS package so that I can take data from several tables then post the data I choose to an excel spreadsheet. That part is pretty simplistic – here’s where it gets confusing (for me):

I have 2 tables:
1. CatalogRelationships
2. CatalogProducts

CatalogRelationships has the following example data:
from_oid to_oid Name
----------- ------- -------
300 101 CS1
300 208 CS3
300 352 CS5
200 187 CS6
200 275 CS9
200 336 US1


CatalogProducts has the following example data:

Oid ProductID Name
---- ------------- -------
101 9001234 Widget
102 9003456 Widget1
103 9007868 Widget2
187 9004567 Widget3
200 9000131 Widget4
208 9007584 Widget5
275 9005647 Widget6
300 9000097 Widget7
336 9005636 Widget8
352 9004444 Widget9

oid should join from_oid and to_oid

I need to return:
ProductID(from) Name(from) ProductID(to) Name(to)
--------------------------------------- --------------------------
9000097 Widget7 9001234 Widget
9000097 Widget7 9007584 Widget5
9000097 Widget7 9004444 Widget9
9000131 Widget4 9004567 Widget3
9000131 Widget4 9005647 Widget6
9000131 Widget4 9005636 Widget8

Any help or suggestions would sincerely be appreciated…

Thx.
RogueSuit

 

[tlbroadbent]

Try this query where CatalogRelationships is joined to CatalogProducts by from_oid and to another occurrence of CatalogProducts by to_oid.

Select f.ProductID As ProductIDFrom, f.Name As NameFrom,
t.ProductID As ProductIDTo, t.Name As NameTo
From CatalogRelationships a
Inner Join CatalogProducts f On a.from_oid=f.oid
Inner Join CatalogProducts t On a.to_oid=t.oid
Terry

http://members.home.net/tlbroadbent/index.htm

 

[RogueSuit]

Thanks Terry....that worked perfectly!