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/subcategory 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 without 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