tblOrgSystem
OrgSystemID OrgSystem
1 USA
2 CANADA
tblOrgSystemNod e
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
tblOrgSystemNod eParent
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.Or gSystem, tblOrgSystemNod e.OrgNode,
OrgNodeParentID from
tblOrgSystemNod eParent join tblOrgSystem on tblOrgSystem.Or gSystemID =
tblOrgSystemNod eParent.OrgSyst emID join tblOrgSystemNod e on
tblOrgSystemNod e.OrgNodeID = tblOrgSystemNod eParent.OrgNode ID
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....
8  1492 
I figured the query out, simple actually:S
select tblOrgSystem.Or gSystem, A.OrgNode,
B.OrgNode from
tblOrgSystemNod eParent join tblOrgSystem on tblOrgSystem.Or gSystemID =
tblOrgSystemNod eParent.OrgSyst emID join tblOrgSystemNod e A on
A.OrgNodeID = tblOrgSystemNod eParent.OrgNode ID
join tblOrgSystemNod e B on
B.OrgNodeID = tblOrgSystemNod eParent.OrgNode ID
However I still can't figure the XML portion of this out, I would like
it to look like this:
USA
Manistique
M-Sales
M-IT
Houston
H-Sales
H-IT
CANADA
etc....
I want to bind this to a treeview so it can be easily navigated.
rhaazy (rh****@gmail.c om) writes:
I figured the query out, simple actually:S
select tblOrgSystem.Or gSystem, A.OrgNode,
B.OrgNode from
tblOrgSystemNod eParent join tblOrgSystem on tblOrgSystem.Or gSystemID =
tblOrgSystemNod eParent.OrgSyst emID join tblOrgSystemNod e A on
A.OrgNodeID = tblOrgSystemNod eParent.OrgNode ID
join tblOrgSystemNod e B on
B.OrgNodeID = tblOrgSystemNod eParent.OrgNode ID
However I still can't figure the XML portion of this out, I would like
it to look like this:
USA
Manistique
M-Sales
M-IT
Houston
H-Sales
H-IT
CANADA
etc....
That does not look like XML to me.
I want to bind this to a treeview so it can be easily navigated.
Maybe treeviews eat XML documents for lunch, but this a newsgroup about
server software, and we don't know about GUIs. At least I don't.
So please post the XML document you want as output. For a tested query,
it's also helps if you post CREATE TABLE statements for you tables, and
sample data as INSERT statements. (And my experience of FOR XML ELEMENT
is limited, so I need to test before I make a fool of myself in public.)
Also, please specify which version of SQL Server you are using.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
<root>
<USA>
<Manistique>
<M-Sales/>
<M-IT/>
</Manistique>
<Houston>
<H-Sales/>
<H-IT/>
</Houston>
</USA>
<CANADA>
<Toronto>
etc...
</root>
Hopefully this format is possible...
Erland Sommarskog wrote:
rhaazy (rh****@gmail.c om) writes:
I figured the query out, simple actually:S
select tblOrgSystem.Or gSystem, A.OrgNode,
B.OrgNode from
tblOrgSystemNod eParent join tblOrgSystem on tblOrgSystem.Or gSystemID =
tblOrgSystemNod eParent.OrgSyst emID join tblOrgSystemNod e A on
A.OrgNodeID = tblOrgSystemNod eParent.OrgNode ID
join tblOrgSystemNod e B on
B.OrgNodeID = tblOrgSystemNod eParent.OrgNode ID
However I still can't figure the XML portion of this out, I would like
it to look like this:
USA
Manistique
M-Sales
M-IT
Houston
H-Sales
H-IT
CANADA
etc....
That does not look like XML to me.
I want to bind this to a treeview so it can be easily navigated.
Maybe treeviews eat XML documents for lunch, but this a newsgroup about
server software, and we don't know about GUIs. At least I don't.
So please post the XML document you want as output. For a tested query,
it's also helps if you post CREATE TABLE statements for you tables, and
sample data as INSERT statements. (And my experience of FOR XML ELEMENT
is limited, so I need to test before I make a fool of myself in public.)
Also, please specify which version of SQL Server you are using.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
rhaazy (rh****@gmail.c om) writes:
><root>
<USA>
<Manistique>
<M-Sales/>
<M-IT/>
</Manistique>
<Houston>
<H-Sales/>
<H-IT/>
</Houston>
</USA>
<CANADA>
<Toronto>
etc...
</root>
Hopefully this format is possible...
It does not seem to be. When I look at the examples for FOR XML EXPLICIT
and FOR XML PATH, they don't lead to that result. And indeed the XML
document above is sort of funny. A more normal document would be:
<ROOT>
<OrgSystem Name="USA">
<UpperOrgNode Name="Manistiqu e">
<LowerOrdNode Name="M-Sales"/>
<LowerOrdNode Name="M-IT"/>
</UpperOrgNode>
etc.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
The problem with this is that there is no upper and lower, the number
or depth if you will of orgnodes is limitless... This is what my major
problem is, figuring out how to get this sort of format (i was giving
more less a psuedo definition of what I need) when say for example
M-Sales gets has a child added to it via the database admin.
Hopefully this format is possible...
It does not seem to be. When I look at the examples for FOR XML EXPLICIT
and FOR XML PATH, they don't lead to that result. And indeed the XML
document above is sort of funny. A more normal document would be:
<ROOT>
<OrgSystem Name="USA">
<UpperOrgNode Name="Manistiqu e">
<LowerOrdNode Name="M-Sales"/>
<LowerOrdNode Name="M-IT"/>
</UpperOrgNode>
etc.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
rhaazy (rh****@gmail.c om) writes:
The problem with this is that there is no upper and lower, the number
or depth if you will of orgnodes is limitless... This is what my major
problem is, figuring out how to get this sort of format (i was giving
more less a psuedo definition of what I need) when say for example
M-Sales gets has a child added to it via the database admin.
One thing is clear, you can never get the answer you are looking for,
if you only hand out tidbits of information. For instance, some posts
back I asked which version of SQL Server you are using, but you never
told us.
Really what is the best way to describe a hierachical structure of
any depth in XML, I don't know. You may want talk to the people in
microsoft.publi c.sqlserver.xml .
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
I apologize for my inability to explain what I mean in such a technical
manner I am not accustomed to. I am an intern and with little
programming experience have managed to work 4 very intense months of
not only programming but designing application software for a major
petroleum company. I have only 4 months of real software development,
however I feel this 4 months has truely given me a head start in my
professional career.
In reference to the version of SQL question, the reason I didn't
respond is because I merely wanted what you clearly re-stated for me in
your last post... -_-'
Also as I have mentioned clearly before, I appreciate everyone's help
in my learning process and can only suggest one thing; try to be more
curtious to someone who may not be familiar with programming, the
internet, or even computers in general! I appreciate the "tough love"
in some cases, but most it only causes frustration (and the occasional
laugh off with my co-worker).
Again, thanks everyone for all your help, I always know where to go
when I need anything answered.
One thing is clear, you can never get the answer you are looking for,
if you only hand out tidbits of information. For instance, some posts
back I asked which version of SQL Server you are using, but you never
told us.
rhaazy wrote:
In reference to the version of SQL question, the reason I didn't
respond is because I merely wanted what you clearly re-stated for me in
your last post... -_-'
That sort of information often affects the answer (even if it happened
not to do so in this particular case). Giving the info and having it
turn out to be irrelevant is much better than omitting it and having
it turn out to be relevant. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: sah |
last post by:
I need some help with the following query:
DECLARE @SRV VARCHAR(20), @date smalldatetime
SET @SRV = (select @@servername)
SET @date = '20040901'
select Srv_Name = @SRV, DB_Name = 'DB_NAME', Table_Name =
'Info_Table', Date_of_Records = @date,
count(*) AS 'Actual Total' ,
|
by: Martin Hampl |
last post by:
Hi,
I am using PostgreSQL 7.4, but I did have the same problem with the
last version.
I indexed the column word (defined as varchar(64)) using lower(word).
If I use the following query, everything is fine, the index is used and
the query is executed very quickly:
select * from token where lower(word) = 'saxophone';
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values...
|
by: Rated R1 |
last post by:
I wrote this before in the NGs, so I am going to paste the responses that I got
and see if someone can please help me. Email me and we can set something up as
Id even be willing to pay for your time to get me to learn this procedure:
MY ORIGINAL POST:
I am trying to create a database for my small business. I have typed a bunch
of information in a table in 5 columns (style, mm, karat, quantity, labor
cost). We have different labor...
|
by: Takeadoe |
last post by:
Dear NG,
Can someone assist me with writing the little code that is needed to
run an update table query each time the database is opened? From what
I've been able to glean from this group, the Autoexec Macro looks like
the way to go. Could someone please assist?
Thank you very much!
Mike
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
