Solved

Query for Flattening SubTypes from a SuperType / SubType Model  Question How-To

Posted on 2004-09-29
15
580 Views
Last Modified: 2010-08-05
NEWBIE SQL QUESTION:

I am working on a typical SuperType / SubType model to use in building a flexible schema for an enterprise type application.  
I am not great at SQL and I need a query that flattens the data in the SubTypes based on the SuperType key.

An example of the tables and table structures for a typical case where a [Site] entity is comprised of one, or more [GeographicBoundary] entities and each [GeographicBoundary] entity type will have many different purposes in the model is shown below.

Using the example data shown below, what is the best query approach to return a single record with data from all SubTypes based on a single SiteID that looks like this:


SiteCode,   SiteDescription,    CountryDescription,      StateDescription,        CityDescription

 'HQ'       'Home Office'       'United States'      'North Carolina'      'Charlotte'
 
 
 Thanks in advance, I appreciate the help...
 
 seaqualnewbie...
 
SEE BELOW  VVVV

-----------------------------------------------------------------------------------------

SITES:

create table "lfoSites" (
      "SiteID" int not null,
      "SiteCode" nvarchar(10) not null,
      "SiteDescription" nvarchar(40) not null,
      "MetropolitanServiceAreaID" int null,
      "BusinessRegionID" int null) ON 'PRIMARY'  

go

alter table "lfoSites"
      add constraint "lfoSites_PK" primary key clustered ("SiteID")
      
      
EXAMPLE DATA:

SiteID = 1
SiteCode = 'HQ'
SiteDescription = 'Home Office'
MetropolitanServiceAreaID  = 34
BusinessRegionID = 1

-----------------------------------------------------------------------------------------

ASSOCIATION: SiteGeographicBoundaries

create table "lfoSiteGeographicBoundaries" (
      "SiteID" int not null,
      "GeographicBoundaryID" int not null) ON 'PRIMARY'  

go

alter table "lfoSiteGeographicBoundaries"
      add constraint "lfoSiteGeographicBoundaries_PK" primary key clustered ("SiteID", "GeographicBoundaryID")  

go

EXAMPLE DATA:

SiteID            GeographicBoundaryID

  1                        1
  1                        2
  1                        3
-----------------------------------------------------------------------------------------

SUPERTYPE: GEOGRAPHICBOUNDARIES

create table "lfoGeographicBoundaries" (
      "GeographicBoundaryID" int not null,
      "GeographicBoundaryTypeID" int not null) ON 'PRIMARY'  

go

alter table "lfoGeographicBoundaries"
      add constraint "lfoGeographicBoundaries_PK" primary key clustered ("GeographicBoundaryID")  

go

EXAMPLE DATA:

GeographicBoundaryID      GeographicBoundaryTypeID

      1                                    1 (= Country Type)
      2                                    2 (= State Type)            
      3                                    3 (= City Type)
-----------------------------------------------------------------------------------------

SUBTYPE: COUNTRIES:

create table "lfoCountries" (
      "GeographicBoundaryID" int not null,
      "CountryCode" nvarchar(10) not null,
      "CountryDescription" nvarchar(40) not null) ON 'PRIMARY'  

go

alter table "lfoCountries"
      add constraint "lfoCountries_PK" primary key clustered ("GeographicBoundaryID")  
      
go


EXAMPLE DATA:

GeographicBoundaryID = 1
CountryCode = 'USA'
CountryDescription = 'United States'

-----------------------------------------------------------------------------------------

SUBTYPE: STATES:

create table "lfoStates" (
      "GeographicBoundaryID" int not null,
      "StateCode" nvarchar(10) not null,
      "StateDescription" nvarchar(40) not null) ON 'PRIMARY'  

go

alter table "lfoStates"
      add constraint "lfoStates_PK" primary key clustered ("GeographicBoundaryID")  

go

EXAMPLE DATA:

GeographicBoundaryID = 2
StateCode = 'NC'
StateDescription = 'North Carolina'

-----------------------------------------------------------------------------------------

SUBTYPE: CITIES:


create table "lfoCities" (
      "GeographicBoundaryID" int not null,
      "CityCode" nvarchar(10) not null,
      "CityDescription" nvarchar(40) not null) ON 'PRIMARY'  

go

alter table "lfoCities"
      add constraint "lfoCities_PK" primary key clustered ("GeographicBoundaryID")  

go

EXAMPLE DATA:

GeographicBoundaryID = 3
CityCode = '22'
CityDescription = 'Charlotte'

-----------------------------------------------------------------------------------------
0
Comment
Question by:bdcox
  • 7
  • 4
15 Comments
 
LVL 15

Expert Comment

by:justinbillig
ID: 12181852
why not just do this


create table THierarchy
(
           intHierarchyID                INTEGER               NOT NULL
           strHierarchyValue           VARCHAR( 100 )               NOT NULL
           intParentID                    INTEGER               NOT NULL
          CONSTRAINT THierarchy_PK PRIMARY KEY ( intHierarchyID )
)

-- Foreign Key
ALTER TABLE THierarchy ADD CONSTRAINT THierarchy_THierarchy_FK
FOREIGN KEY ( intParentID ) REFERENCES THierarchy ( intHierarchyID )


-- Default inser the root level
INSERT INTO THierarchy( intHierarchyID, strHierarchyValue, intParentID )
VALUES( 0, 'Root Level', 0 )

Then all you need to do is insert the records and its parent
0
 

Author Comment

by:bdcox
ID: 12182385
I actually have a secondary table that is setup to do this but I'm not sure how to query against it to get the result I'm looking for...

Here is what I have tried, but I get a ragged result... I need a flattend result...

SELECT
si.SiteID,
si.SiteCode,
si.SiteDescription,
sg.GeographicBoundaryID,
co.CountryDescription,
st.StateDescription,
ct.CityDescription
FROM lfoSiteGeographicBoundaries sg
LEFT JOIN lfoSites si ON sg.SiteID = si.SiteID
LEFT JOIN lfoCountries co ON sg.GeographicBoundaryID = co.GeographicBoundaryID
LEFT JOIN lfoStates st ON sg.GeographicBoundaryID = st.GeographicBoundaryID
LEFT JOIN lfoCities ct ON sg.GeographicBoundaryID = ct.GeographicBoundaryID
WHERE sg.SiteID = 1;

Example Result Set:

1      HQ      Home Office      1      NULL                             NULL                      NULL
1      HQ      Home Office      2      United States of America    NULL                      NULL
1      HQ      Home Office      3      NULL                             NULL                      NULL
1      HQ      Home Office      4      NULL                             North Carolina      NULL
1      HQ      Home Office      6      NULL                             NULL                      NULL
1      HQ      Home Office      7      NULL                             NULL                      Charlotte
1      HQ      Home Office      8      NULL                             NULL                      NULL
1      HQ      Home Office      9      NULL                             NULL                      NULL

How would the method you suggest help...  What would the query be to achieve the flattend results...?


0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12182612
You're almost there. You need to drive it from Sites and you need to restrict each join by GeographicBoundaryTypeID.

SELECT
si.SiteID,
si.SiteCode,
si.SiteDescription,
sg.GeographicBoundaryID,
co.CountryDescription,
st.StateDescription,
ct.CityDescription
FROM lfoSites si
LEFT JOIN lfoSiteGeographicBoundaries sg ON sg.SiteID = si.SiteID
LEFT JOIN lfoCountries co ON sg.GeographicBoundaryID = co.GeographicBoundaryID and sg.GeographicBoundaryTypeID = 1
LEFT JOIN lfoStates st ON sg.GeographicBoundaryID = st.GeographicBoundaryID and sg.GeographicBoundaryTypeID = 2
LEFT JOIN lfoCities ct ON sg.GeographicBoundaryID = ct.GeographicBoundaryID and sg.GeographicBoundaryTypeID = 3
WHERE sg.SiteID = 1;
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12182661
oops. Just realised the GeographicBoundaryTypeID come from another table.
Will fix that now.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12182764
SELECT
si.SiteID,
si.SiteCode,
si.SiteDescription,
L.CountryDescription,
S.StateDescription,
C.CityDescription
FROM lfoSites si
LEFT JOIN (select co.CountryDescription, sg.SiteID)
      from lfoSiteGeographicBoundaries sg inner join lfoCountries co inner join ON sg.GeographicBoundaryID = co.GeographicBoundaryID
      inner join lfoGeographicBoundaries gb on sg.GeographicBoundaryID = gb.GeographicBoundaryID
      where gb.GeographicBoundaryTypeID = 1) L ON L.SiteID = si.SiteID
LEFT JOIN (select co.CountryDescription, sg.SiteID)
      from lfoSiteGeographicBoundaries sg inner join lfoStates st inner join ON sg.GeographicBoundaryID = st.GeographicBoundaryID
      inner join lfoGeographicBoundaries gb on sg.GeographicBoundaryID = gb.GeographicBoundaryID
      where gb.GeographicBoundaryTypeID = 2) S ON S.SiteID = si.SiteID
LEFT JOIN (select ci.CountryDescription, sg.SiteID)
      from lfoSiteGeographicBoundaries sg inner join lfoCities ci inner join ON sg.GeographicBoundaryID = ci.GeographicBoundaryID
      inner join lfoGeographicBoundaries gb on sg.GeographicBoundaryID = gb.GeographicBoundaryID
      where gb.GeographicBoundaryTypeID = 1) C ON C.SiteID = si.SiteID
WHERE si.SiteID = 1;
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12182844
A site can be associated with several GBs. For your question to be solvable, it needs to be associated with at most 1 GB for each type of GB i.e. each site has at most 1 city, 1 state, 1 country.
That constraint is not implemented. It could be done by putting the GeographicBoundaryTypeID  into the Site-GB association with a PK on (SiteID, GeographicBoundaryTypeID) .
That would simplify a lot of queries.


0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:bdcox
ID: 12183279
Is there something wrong with the syntax on this last query you sent?  Are you missing some right parentheses?  Most of this looks like it should work, but I'm confused about the nested joins and I'm getting errors in from clauses on lines, 10, 14, 16...

Can you clarify the syntax...  So I can try this...

I like the comment about the GeographicBoundaryTypeID but I already have that association with GeograhpicBoundary in the GeographicBoudaries  Table...?

0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12184896
SELECT
si.SiteID,
si.SiteCode,
si.SiteDescription,
L.CountryDescription,
S.StateDescription,
C.CityDescription
FROM lfoSites si
LEFT JOIN (select co.CountryDescription, sg.SiteID
      from lfoSiteGeographicBoundaries sg inner join lfoCountries co  ON sg.GeographicBoundaryID = co.GeographicBoundaryID
      inner join lfoGeographicBoundaries gb on sg.GeographicBoundaryID = gb.GeographicBoundaryID
      where gb.GeographicBoundaryTypeID = 1) L ON L.SiteID = si.SiteID
LEFT JOIN (select st.StateDescription, sg.SiteID
      from lfoSiteGeographicBoundaries sg inner join lfoStates st  ON sg.GeographicBoundaryID = st.GeographicBoundaryID
      inner join lfoGeographicBoundaries gb on sg.GeographicBoundaryID = gb.GeographicBoundaryID
      where gb.GeographicBoundaryTypeID = 2) S ON S.SiteID = si.SiteID
LEFT JOIN (select ci.CityDescription, sg.SiteID
      from lfoSiteGeographicBoundaries sg inner join lfoCities ci  ON sg.GeographicBoundaryID = ci.GeographicBoundaryID
      inner join lfoGeographicBoundaries gb on sg.GeographicBoundaryID = gb.GeographicBoundaryID
      where gb.GeographicBoundaryTypeID = 1) C ON C.SiteID = si.SiteID
WHERE si.SiteID = 1;

0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12185068
yuour associations permits this scenario:

Site, GB
s1, gb1
s1,  gb2

GB, GBtype
gb1, city
gb2, city


So s1 is a GB of type city twice over:
s1->gb1->city
s2->gb2->city

What you might want is for  each site to be part of various GBs but only of one GB for any type.

A Site is arguably in only one city.
A Site is arguably in only one state.
A Site is arguably in only one country.

So if we have 3 types, then we have at most 3 GBs for each Site.

That means the realtionship is between Site X GB Type on the one hand and GB on the other.
0
 

Author Comment

by:bdcox
ID: 12189384
Yes, you are correct.  What I'm esentially trying to do is to role up a series of GeographicBoundaries into a single entity named Site.  
There is also a GeographicBoundary Hierarchy table that is not shown in my example that documents the hierarchical relationships between Geographical Boundaries.  The Site entity is used to simplify the user interface code.  The user can select a known site name and the rest of the information is derived from the Site/Geographic Boundaries Association and the Geographic BoundariesHierarchy tables.
 
I need the SubType structure to be flexible so that I can easily add new types in the future and, each SubType may have different attributes for other uses in the application.  In other words I need the City type to remain as a pure type.  

In fact there are many more types.  Also, the number of different types is unknown at design time.  My goal is to be able to allow the ability to add new types by adding a new SubType table, genearting a new key from the GeographicalBoundaries, update the Site/GeographicBoundaries Association Table and the GeographicBoundariesHierarchy table and adjust the query code as necessary to suppor the new interface.

Ideally, the approach would automatically determine from the structure the left to right nature of the SubTypes based on the Hierarchy table and spit out the results no matter how many SubTypes there are.  I'm not sure if this can be done effieciently in SQL but I would like to design the structure to get as far as possible.

I wish there was a way to embed images in this forum, upload a image of the schema diagram...

0
 

Author Comment

by:bdcox
ID: 12189416
Also, note that the revised query above did run successfuly but returned no data.  It appears that it is flattening the structure but I don't end up with any data from the test case.  I'm still reviewing the solution to fully understand it...

Thanks...
0
 
LVL 12

Accepted Solution

by:
monosodiumg earned 125 total points
ID: 12189798
>I wish there was a way to embed images in this forum, upload a image of the schema diagram...
The preferred approach is to uplaod them somehwere else and post a link.

>here is also a GeographicBoundary Hierarchy
Say we take Site1 and find using our query that it is in GB "City1" and in GB "State1" and in GB "Country1" using our query. We could also check the GB hierarchy and find out what the parent and grandparent GBs are for GB "City1". We now have 2 potential states and 2 potential countries for Site1. That could be a problem.
If you have a GB hierarchy, then why do you need a 1-many association between Sites and GBs? You could just associate each Site with most speciifc GB to which it is related and retrieve the larger GBs via the hierarchy.

>each SubType may have different attributes for other uses in the application
Imagine you have to list a bunch of GBs of mixed types in a single listing in a GUI. If that is a posiblity then you might require some minimum common info for all subtypes, for example, a short name, a full name, a description, an abbreviation code which would be best placed into the GB table.

> the left to right nature
Don't understand that. Do you mean smallest/narrowest/most speciifc to largest/widest/most general e.g. city to country?
Hierarchies, especially of arbitrary (not fixed) or variable (ragged) depth, are tricky in SQL. They tend to require iterated querying to move up or down the tree.  At least if you use the usual adjaceny model (node, parent_node) model. For examaple, getting the path from a node to the root or getting all the descendents of a node require recursion up or down the tree and these are usually implemented by iterative queryi9ng with temp tables etc whihc all gets a bits messy and slow.
There is an alternative model called "nested sets" that avoids a lots of iteration at the expense of more complex code for modifyng the tree. If your hierarchy is fixed or you only need to add nodes, then that might be worthwhile.

Are you expecting to add new subtypes at runtime? That creates a bunch of problems of it's own. Lots of dynamic SQL (not good), questions about how db clients as can acces these new types .... An alternative with a different set of problems (no free lunches here !) is to have one single type and an Attributes system which can handle string, int etc attributes by name. So you have something like Sites -> AttributeValues <- Attributes. That exchanges dynamic tweaking of the schema for dynamic attribute hanlding (SQL svr does this kind of thing internally for represneting tables for example). You pays your money and you takes your choice.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now