July 9, 2008 at 8:34 am
Hi all. I'm working in SQL Server 2005 and ASP.NET 2.0. I have six tables, each of which reference its parent table via a foreign key:
Division
Subdivision
Category
Subcategory
Component
Subcomponent
I want to return parent/child rows and bind them to a Treeview control. My question, is there any way to do this via a single or set of queries so that I can simply bind the resulting to the Treeview control?
Your help is appreciated,
J
July 9, 2008 at 9:05 am
You can do this with a simple set of left outer joins, I would think. Otherwise, it's Union All statements.
To be more specific, I would need to know the table structures, and the desired output from the query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 9:51 am
GSquared, thanks for the help. Here is the table structure. For simplicity I left out a lot of fields that aren't needed for the query and the foreign key structure (pretty easy to see) The hierarchy is linked to a company and automobile table.
CREATE TABLE #Company
(
CompanyID int IDENTITY(1, 1) PRIMARY KEY,
CompanyName varchar(24) NOT NULL
)
CREATE TABLE #Automobile
(
AutomobileID int IDENTITY(1, 1) PRIMARY KEY,
AutomobileName varchar(24) NOT NULL
)
CREATE TABLE #Division
(
DivisionID int IDENTITY(1, 1) PRIMARY KEY,
DivisionName varchar(24) NOT NULL,
DivisionNumericID varchar(4) NOT NULL,
fkCompanyID int NOT NULL,
fkAutomobileID int NOT NULL
)
CREATE TABLE #Subdivision
(
SubdivisionID int IDENTITY(1, 1) PRIMARY KEY,
SubdivisionName varchar(24) NOT NULL,
SubdivisionNumericID varchar(4) NOT NULL,
fkDivisionID int NOT NULL
)
CREATE TABLE #Category
(
CategoryID int IDENTITY(1, 1) PRIMARY KEY,
CategoryName varchar(24) NOT NULL,
CategoryNumericID varchar(4) NOT NULL,
fkSubdivisionID int NOT NULL
)
CREATE TABLE #Subcategory
(
SubcategoryID int IDENTITY(1, 1) PRIMARY KEY,
SubcategoryName varchar(24) NOT NULL,
SubcategoryNumericID varchar(4) NOT NULL,
fkCategoryID int NOT NULL
)
CREATE TABLE #Component
(
ComponentID int IDENTITY(1, 1) PRIMARY KEY,
ComponentName varchar(24) NOT NULL,
ComponentNumericID varchar(4) NOT NULL,
fkSubcategoryID int NOT NULL
)
CREATE TABLE #Subcomponent
(
SubcomponentID int IDENTITY(1, 1) PRIMARY KEY,
ComponentName varchar(24) NOT NULL,
ComponentNumericID varchar(4) NOT NULL,
fkComponentID int NOT NULL
)
The desired output would be like this:
Division ( Division ID, DivisionName, DivisionNumericID )
Subdivision ( SubdivisionID, SubdivisionName, SubdivisionNumericID )
Category ( CategoryID, CategoryName, CategoryNumericID )
Subcategory ( SubcategoryID, SubcategoryName, SubcategoryNumericID )
Component ( ComponentID, ComponentName, componentNumericID )
Subcomponent ( SubcomponentID, SubcomponentName, SubcomponentNumericID)
The WHERE clause of the query would contain:
WHERE
#Division.fkAutomobileID = @fkAutomobileID
AND
#Division.fkCompanyID = @fkCompanyID
Thanks,
J
July 9, 2008 at 12:27 pm
I'm going to say your best bet is a Union All type query. (As a side note, I find it interesting that "DivisionID" is numeric, while "DivisionNumericID" is a character string. Doesn't much matter, but it is an interesting design.)
It will end up looking something like:
select 1 as Lvl, -- Divisions
DivisionID,
null as SubdivisionID,
null as CategoryID,
null as SubcategoryID,
null as ComponentID,
null as SubComponentID,
DivisionNumbericID,
DivisionName
from dbo.Division
union all
select 2, -- Subdivisions
fkDivisionID,
SubdivisionID,
null,
null,
null,
null,
SubdivisionNumericID,
SubdivisionName
from dbo.Subdivision
union all
select 3, -- Categories
DivisionID,
fkSubdivisionID,
CategoryID,
null,
null,
null,
CategoryNumericID,
CategoryName
from dbo.Category
inner join dbo.Subdivision
on Category.fkSubdivisionID = Subdivision.SubdivisionID
union all
... through each of the tables
I'm sure you can see the pattern there. You might need to extend the column offset (divisionID, SubdivisionID, etc.) to the NumericIDs, by using the same "null as ..." pattern.
Does that make sense? Will it do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 4:38 pm
I am not sure you want to return all the data in one go. Usually people add nodes as the are "expanded" and the child(S) are empty. The reason is not to kill the server with a very large amount of data that could potentially not be even used.
* Noel
July 10, 2008 at 11:49 am
Yeah, you add input parameters and Where clauses to it, obviously.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply