SuperType SubType Flattening Query Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SuperType SubType Flattening Query Question

Author

Topic

bcox
Starting Member

6 Posts

Posted - 2004-09-29 : 12:31:52

NEWBIE 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...

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

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'

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 12:54:48

of the top of my head use left join:
select ...
lfoSiteGeographicBoundaries t1
left join GEOGRAPHICBOUNDARIES t2 on t1.GeographicBoundaryID = t2.GeographicBoundaryID
left join .... -- other tables
where your condition.

Go with the flow & have fun! Else fight the flow

bcox
Starting Member

6 Posts

Posted - 2004-09-29 : 13:35:32

I tried the following, but I get a ragged result. I need to flatten the result into a single record...

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;

bcox
Starting Member

6 Posts

Posted - 2004-09-29 : 13:41:02

Example Results From the Last Post...

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources