471,887 Members | 1,555 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,887 software developers and data experts.

help with bottom to top query into hierarchical data

Hello to all:

First let me apologize for the length of this question, I've made an attempt
to include as much information as is needed to help with the question.

I am having problems putting together a query to pull out an alternative
hierarchical view of my data. The database is implemented under SQL Sever
2000 and I am writing the front end using VB.Net and ADO.net. The following
is the portion of my database structure that I am having problems with. In
this diagram
each table has a one to many relationship with the table beneath it (The
primary key for each table is the table name with ID appended, and the
foreign keys for each child table refernce the primary key in the table

Offers |
| | |
| |__OfferDetails
| |
Acceptances |
| |
Notice that an Offer and it's associated Acceptances can be in more than one
Contract, the OfferDetails describe how the Offer is broken up into each
While there is a one to many relationship between Contracts and
OfferDetails, there would only ever be a single OfferDetail per Contract per
Offer (i.e. splitting up the Offer means assigning one and only one
OfferDetail to each Contract it is assigned to). This info may not be
relavant, but it can be counted on as true. This doesn't hold true with the
AcceptanceDetails however, there can be multiple AcceptanceDetails per
Acceptance associated with OfferDetails.

I wish to establish a couple of different heierachical grids into this data
as part of my user interface:

View by Deals (In this view we see only the part of Each Offer associated
with the Deal at the top of the
Offer1 + OfferDetails associated with Contract1
Acceptance1 + AcceptanceDetails assoctaited with OfferDetails above.

Offer1 + OfferDeatils assoctaited with Contract2
Acceptance1 + AcceptanceDetails associated with OfferDetails above.

This grid has been relativly straitforward to figure out, as the hierrchical
information is already
structured to plug it into a hierrchical grid control easily (after
combining the Offers and OfferDetails
into a new table).
View by Offer/Acceptance (In this view we see the entire Offer+Acceptance as
the top of the hierarchy,
and each Deal (and associated Contract) underneath the Offer+Acceptance

Offer1+Acceptance1 (with all the details rolled up into a summuray included
in this row).
This is the grid which is causing me problems, the problem area being
putting together a query which retrieves each DealID assoctiated with the
OfferID at the top of the hierarchy. I need a query which essentially
Starts with the OfferID, pulls each OfferDetailID associated with it, then
moves upstream to
find each ContractID assoctaed with the OfferDetailsID (saving those ID's
for use in third level of the grid, since there can be more Contracts under
the Deal which are not associated with the Offer we're looking at), and then
moves upstream again to finally get the DealID which forms the second level
of the hierarchy.

Can somebody help me with what such a query (or queries, as I assume I'll
need one to get the DealID's and another to get the ContractID's, although
the former might use the latter as a sub-query) might look like? I'm not at
all good with T-SQL, but I'm learning fast! I haven't been able to find any
examples on
how to go backwards up a tree to get to a record. I'll most likely be
implementing these as stored procedures, as these grid views are integral to
my user

Thanks in advance to any who take the time to reply!

Jul 20 '05 #1
0 3961

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Cláudia Morgado | last post: by
5 posts views Thread by clintonG | last post: by
3 posts views Thread by Marc | last post: by
8 posts views Thread by pamelafluente | last post: by
1 post views Thread by cmgmyr | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.