unique data view  
Author Message
AssiArev





PostPosted: Top

SQL Server Developer >> unique data view

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

Appliances - Kitchen {EEIU009833551238700909}

Appliances - Laundry (VVVC032327659302846593}



Thanks for your help.



Regards,

Don

SQL Server26