473,390 Members | 1,141 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 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
above).

Deals
|
Contracts___
|
Offers |
| | |
| |__OfferDetails
| |
Acceptances |
| |
|__AcceptanceDetails
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
Contract.
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
hierarchy):
Deal1
Contract1
Offer1 + OfferDetails associated with Contract1
Acceptance1 + AcceptanceDetails assoctaited with OfferDetails above.

Deal2
Contract2
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).
Deal1
Contract1
Deal2
Contract2
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
interface.

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


Jul 20 '05 #1
0 4093

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: MultiTaskinG | last post by:
I want to retrieve all comment stored from my web users ordered BY THREAD and BY TIMESTAMP (INT 11) with a single query (if is possible) now I launch this query: SELECT thread, timestamp,...
3
by: ClŠudia Morgado | last post by:
Hello! Oracle has the option with the SQL CONECT BY statement to run through a hierarchical database with a single SQl-statement: <!--SQL SELECT ms_id,ms_parent FROM messages CONNECT BY PRIOR...
15
by: Richard Hollenbeck | last post by:
I tried to ask this question before on the 14th of January but I never got a reply. I'm still struggling with the problem. I'll try to rephrase the question: I have a crosstab query with rows...
5
by: clintonG | last post by:
I'm looking for documentation and would not turn my nose up to any code from anybody who thinks they are good at the design of an algorythm that can be used to generated a hierarchical relational...
3
by: Marc | last post by:
Hi everyone, I'm trying to build a Thesaurus. In my thesaurus i've created the following tables: terms, btnt (broader terms/narrower terms), rt (related terms) The structures of the tables...
8
by: pamelafluente | last post by:
I am beginning aspNet, I know well win apps. Need a simple and schematic code example to start work. This is what I need to accomplish: ---------------------- Given button and a TextBox on a...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
1
by: cmgmyr | last post by:
Hey All, I'm having a little problem with exporting data from my database. The problem is that the query seems to be too much for PHP and MySQL to handle since it is timing out. I have the timeout...
1
by: sibani sahu | last post by:
Hi, I want to generate the organisational structure of my company.I have a table /viewwhich has the emp_id,manager_id and the level_num.The level_num represents the designation level of the...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
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...
0
Oralloy
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,...
0
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...

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.