The objective here is to create a SQL data view of two tables that have a on
e
to many relationship. The two tables represent a simple category/subcategor
y
relationship and the common value that will need to be returned will be the
name and its primary key (unique identifier). Let me represent the desired
result by the following example.
This represents the table/field schema:
TABLE 1 – CATEGORY
category_id
name
TABLE 2 – SUBCATEGORY
category_id
name
parent_id (relates to category_id in TABLE 1- CATEGORY)
The following represents the top level CATEGORY sample data:
TABLE 1 – CATEGORY
Name
Foundation
Slab
Rough Shell
Mechanicals
Finish Trades
Appliances
The second representation is the related SUBCATEGORY sample data:
TABLE 1 – SUBCATEGORY
Parent_Name Name
Rough Shell Framing
Rough Shell Windows
Rough Shell Doors
Appliances Kitchen
Appliances Laundry
(Note: The other top level categories do not have subcategories defined)
What is required of the SQL data view is to provide both name and
category_id for each distinct CATEGORY and SUBCATEGORY (see example below).
That specifically requires that each top level CATEGORY be listed explicitly
,
and each CATEGORY that does have an associated SUBCATEGORY will also need to
be listed (with names concatenated with hyphen as example below) both withou
t
its associated SUBCATEGORY records and with the associated SUBCATEGORY
records (along with its specific category_id unique identifier). Using the
two datasets above the required output of the data view would be as follows:
Concatenated Name Category_id
Foundation {QDEP74849393939303020}
Slab {BNIO802274939302020207}
Rough Shell {UUED34128554488326264} ? (Note here the top level category
is
listed in addition to its related records)
Rough Shell – Framing {HGKJ556388920118933933}
Rough Shell – Windows {LLPU112331099789875542}
Rough Shell – Doors {YVXX777546752211456023}
Mechanicals {DSFD44322274747299844}
Finish Trades {SSWQ43271727300998112}
Appliances {MYYB986373722924848433}
(Note here the top level category
is listed in addition to its related records)
Appliances – Kitchen {EEIU009833551238700909}
Appliances – Laundry (VVVC032327659302846593}
Thanks for your help.
Regards,
DonTest this:
SELECT name, category_id FROM Category
UNION
(SELECT LTRIM(RTRIM(a.name))+' - '+LTRIM(RTRIM(b.name)) AS name, b.category_
id
FROM Category a JOIN Subcategory b ON a.category_id = b.parent_id)
ORDER BY name
"dbj" wrote:
> The objective here is to create a SQL data view of two tables that have a
one
> to many relationship. The two tables represent a simple category/subcateg
ory
> relationship and the common value that will need to be returned will be th
e
> name and its primary key (unique identifier). Let me represent the desire
d
> result by the following example.
> This represents the table/field schema:
> TABLE 1 – CATEGORY
> category_id
> name
> TABLE 2 – SUBCATEGORY
> category_id
> name
> parent_id (relates to category_id in TABLE 1- CATEGORY)
>
> The following represents the top level CATEGORY sample data:
> TABLE 1 – CATEGORY
> Name
> Foundation
> Slab
> Rough Shell
> Mechanicals
> Finish Trades
> Appliances
> The second representation is the related SUBCATEGORY sample data:
> TABLE 1 – SUBCATEGORY
> Parent_Name Name
> Rough Shell Framing
> Rough Shell Windows
> Rough Shell Doors
> Appliances Kitchen
> Appliances Laundry
> (Note: The other top level categories do not have subcategories defined)
> What is required of the SQL data view is to provide both name and
> category_id for each distinct CATEGORY and SUBCATEGORY (see example below)
.
> That specifically requires that each top level CATEGORY be listed explicit
ly,
> and each CATEGORY that does have an associated SUBCATEGORY will also need
to
> be listed (with names concatenated with hyphen as example below) both with
out
> its associated SUBCATEGORY records and with the associated SUBCATEGORY
> records (along with its specific category_id unique identifier). Using th
e
> two datasets above the required output of the data view would be as follow
s:
> Concatenated Name Category_id
> Foundation {QDEP74849393939303020}
> Slab {BNIO802274939302020207}
> Rough Shell {UUED34128554488326264} ? (Note here the top level categor
y is
> listed in addition to its related records)
> Rough Shell – Framing {HGKJ556388920118933933}
> Rough Shell – Windows {LLPU112331099789875542}
> Rough Shell – Doors {YVXX777546752211456023}
> Mechanicals {DSFD44322274747299844}
> Finish Trades {SSWQ43271727300998112}
> Appliances {MYYB986373722924848433}
(Note here the top level catego
ry
> is listed in addition to its related records)
> Appliances – Kitchen {EEIU009833551238700909}
> Appliances – Laundry (VVVC032327659302846593}
> Thanks for your help.
> Regards,
> Don
>|||Worked like a charm. Thanks very much!!!
Don
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:52BB0F34-E93A-40BC-BB75-B9A9A765B52F@.microsoft.com...
> Test this:
> SELECT name, category_id FROM Category
> UNION
> (SELECT LTRIM(RTRIM(a.name))+' - '+LTRIM(RTRIM(b.name)) AS name,
> b.category_id
> FROM Category a JOIN Subcategory b ON a.category_id = b.parent_id)
> ORDER BY name
>
> "dbj" wrote:
>
