|
Posted by rhaazy on 08/23/06 20:05
tblOrgSystem
OrgSystemID OrgSystem
1 USA
2 CANADA
tblOrgSystemNode
OrgSystemID OrgNodeID OrgNode
1 3 Manistique
1 4 Houston
1 7 M-Sales
1 8 M-IT
1 10 H-Sales
1 11 H-IT
2 5 Toronto
2 6 Ontario
2 13 T-Sales
2 14 T-IT
2 16 O-Sales
2 17 O-IT
tblOrgSystemNodeParent
OrgSystemID OrgNodeId OrgNodeParentID
1 3 3
1 4 4
1 7 3
1 8 3
1 10 4
1 11 4
2 5 5
2 6 6
2 13 5
2 14 5
2 16 6
2 17 6
I so far have this query.
select tblOrgSystem.OrgSystem, tblOrgSystemNode.OrgNode,
OrgNodeParentID from
tblOrgSystemNodeParent join tblOrgSystem on tblOrgSystem.OrgSystemID =
tblOrgSystemNodeParent.OrgSystemID join tblOrgSystemNode on
tblOrgSystemNode.OrgNodeID = tblOrgSystemNodeParent.OrgNodeID
This Query gives me this result:
OrgSystem OrgNode OrgNodeParentID
USA Manistique 3
USA Houston 4
USA M-Sales 3
USA M-IT 3
USA H-Sales 4
USA H-IT 4
CANADA Toronto 5
CANADA Ontario 6
CANADA T-Sales 5
CANADA T-IT 5
CANADA O-Sales 6
CANADA O-IT 6
I want this result instead!!!!
OrgSystem OrgNode OrgNodeParentID
USA Manistique Manistique
USA Houston Houston
USA M-Sales Manistique
USA M-IT Manistique
USA H-Sales Houston
USA H-IT Houston
CANADA Toronto Toronto
CANADA Ontario Ontario
CANADA T-Sales Toronto
CANADA T-IT Toronto
CANADA O-Sales Ontario
CANADA O-IT Ontario
I cant figure out if I need another join or some kind of union to get
the name of the nodes instead ot the ID....
Also After I figure the query out I need to generate an XML document
that has a heiracheal structure that matches the 'organizational'
structure of the tables....
Navigation:
[Reply to this message]
|