473,890 Members | 1,636 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 |
| |
|__AcceptanceDe tails
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
AcceptanceDetai ls however, there can be multiple AcceptanceDetai ls 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 + AcceptanceDetai ls assoctaited with OfferDetails above.

Deal2
Contract2
Offer1 + OfferDeatils assoctaited with Contract2
Acceptance1 + AcceptanceDetai ls 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+Acceptanc e as
the top of the hierarchy,
and each Deal (and associated Contract) underneath the Offer+Acceptanc e

Offer1+Acceptan ce1 (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 4125

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

Similar topics

1
2432
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, level FROM comments WHERE articleid=16 ORDER BY thread, timestamp,level
3
5594
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 ms_id = ms_parent START WITH ms_id = 1 --> Result-set (example): ms_id parent_id 1 1.1 1 1.1.1 1.1 1.1.2 1.1 1.1.3 1.1 1.2 1 1.2.1 1.2
15
4414
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 of students and columns of activities and the data are the students' scores in each activity. No problem, almost. The problem is that there are five classes at the moment and will be more classes (or courses) in future semesters. I don't want...
5
2259
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 data model. What? A Yahoo-like drill-down menu that is a series of categories and nested categories is a hierarchical relational data model. An example can be seen at but the review of the query string values strongly indicates
3
3472
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 are like this: -------------------------------- | table: terms |
8
2095
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 web form when one presses the button on the web form on a client pc, the sql query which is contained in the text box is sent to a vb net application on a server pc. The win application sends the query to the database, collects the results,
16
3536
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 renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to...
1
1944
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 set right now at 3 minutes. I can run this in phpMyAdmin and it usually takes 4-5 minuted to complete. I do not want to have to make the client wait that long for this. This is part of an import/export Excel function that I have made. Here is...
1
4492
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 employee.So I used a query as:_ select concat(REPEAT('-',a.level_num *5),a.employee_name) as Org_Chart from ALL_EMPLOYEE_VIEW A order BY a.level_num ; This does not create a tree structure but simply arranges the employees according to their...
0
10452
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9618
agi2029
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...
1
8009
isladogs
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...
0
7161
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5837
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...
0
6036
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4667
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
2
4260
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3268
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.