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!
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!