Return Hierarchical Data From One Table (with a JOIN to a second table)
From: David Krussow (dk_at_nospam.com)
Date: 04/14/04
- Next message: Uri Dimant: "Re: First works, second doesn't???"
- Previous message: Pogas: "Urgent - A Select statement to rank a column into quartiles"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 13 Apr 2004 22:36:14 -0700
It seems like more detail is better than less detail in this group; I hope
the following is enough without being too much:
I'm working on logic that implements a dynamically constructed menu (primary
site navigation) in an ASP.NET Web application. The Web application will be
installed in many Web sites. Each installation (Web site) will share the
same database, and each site, at runtime, reads the table below to determine
how to construct the site's particular main menu. The application is
comprised of a number of standard/base modules and optional modules - which
are organized into a hierarchy. Example: one standard/base module would be
"photo albums" (top/root level menu entry), and under that could be a number
of optional sub-categories - "Spring Break" - (second-level menu entry), and
under that a menu entry for each of several years - "2001", "2002", "2003",
"2004" (third-level menu entries). The final/leaf entry would represent the
actual photo album, and would appear at the fourth level in this example.
Each site will have from 4 to 9 root-level menu entries, each of which will
have from zero to 4 child-levels under it.
I would appreciate help in designing an efficient query that will retrieve
the data to be supplied to the menu-construction logic.
Specifically, I'm looking to have a query that returns one result set
containing each parent row followed immediately by any of its own child
rows; and each child row followed immediately by it's child rows in turn.
After the last child row is returned for one "branch", then the next root
row would be returned, and it would be immediately followed by all of its
child rows.... The order in which the rows are returned is determined by 3
columns ([Rank], [SequenceInRank], and [SortHelper]). Please see individual
field descriptions below for more on the sorting requirements. The columns
that need to be
returned include these from tblSiteComponents: [MemberID], [MenuText],
[Rank], and from tblPageTemplates: [FileName].
I'm sure a self-join will get much of what is needed, however a join to a
second table (tblSiteComponentTemplates) is also required to return an
additional column ([FileName]) that will need to
be returned for each leaf-level entry (defined as an entry in
tblSiteComponents where [ComponentTemplateID] is not null).
Please note that I have intentionally denormalized the model a bit in order
to avoid having to join up to 4 tables and possibly execute UNION queries
to get what is required.
CREATE TABLE [dbo].[tblPageTemplates] (
[TemplateTypeID] [int] NOT NULL ,
[ComponentTemplateID] [int] NOT NULL , -- Used in join to
tblSiteComponents
[FileName] [varchar] (50) NOT NULL ,
[DescriptiveName] [varchar] NULL ,
) ON [PRIMARY]
CREATE TABLE [dbo].[tblSiteComponents] (
[ComponentID] [int] IDENTITY (1, 1) NOT NULL ,
[SiteID] [int] NOT NULL ,
[ComponentTypeID] [int] NOT NULL ,
[ComponentTemplateID] [int] NULL , -- Used in join to tblPageTemplates
[MemberID] [int] NULL ,
[MenuText] [varchar] (50) NOT NULL ,
[ParentComponentID] [int] NULL ,
[Rank] [tinyint] NOT NULL ,
[SequenceInRank] [tinyint] NOT NULL ,
[SortHelper] [tinyint] NOT NULL ,
[ActiveStatus] [bit] NULL
) ON [PRIMARY]
Column Descriptions:
[ComponentID] and [SiteID] comprise the primary key. (other indexes are not
yet specified). The result set needs to return rows for only one value of
[SiteID] at a time.
[ComponentTypeID] is not relevant to the particular query this thread is
about... however fyi, it functions as a foreign key against a lookup table
that contains entries for things like "Maps", "Catalogs", "Photo Albums",
"Distributors", "Routes", "Vendors", and other such root-level modules
available in the site.
[ComponentTemplateID] functions as a foreign key against tblPageTemplates
that contains paths to template files on disk (the file name of which needs
to be returned by the query this thread is about). Will be NULL for rows
that represent categories (i.e., NULL for non leaf-level rows).
[MemberID] - needs to be returned by the query; is also a foreign key,
however the query this thread is about does not need to join to any tables
related on this column.
[MenuText] - needs to be returned by the query; the name says plenty about
what it is.
[ParentComponentID] - specifies which row is the logical parent of the
current row. Is NULL for root-level rows only.
[Rank] - needs to be returned by the query, although it appears to be
unnecessary as the information contained here could be inferred via
ParentComponentID, however I have it in the table because the application's
menu construction logic needs it, and I thought it may be useful for ORDER
BY purposes.
[SequenceInRank] - Used strictly for specifying the ORDER in which the items
are to appear in any particular site's menu; and enables the order to be
specified within each rank level (root-level, second-level, and so on). You
can see that many sites may have identical menu components, but each site
can specify sequence independently.
[SortHelper] - The application requirements specify that zeros sort *after*
ones: That is, while we want to sort by Rank and then by SequenceInRank, we
want SequenceInRank values of zero to appear *after* non zero values. Thus
the need for [SortHelper]
[ActiveStatus] - rows with a value of 1 are to be included in the returned
result set, while rows with a value of zero are to be omitted.
--- I appreciate any guidance or samples - even if they are only working models that require some tweaking for my particular situation. DK
- Next message: Uri Dimant: "Re: First works, second doesn't???"
- Previous message: Pogas: "Urgent - A Select statement to rank a column into quartiles"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
