UNION or other solution?

microsoft.public.sqlserver.programming

1 Jul 2006 6:33 PM

Rob Meade

Hi all,

I have a table which contains training course details....

There's a hierarchy with the training courses which is basically this..

A package can contain any number of series', a series can contain any number
of courses...

In the main SKU table, each row has a column, SKUID, this might begin
pkg.xxxx.xxx or srs.xxxx.xxx for example, so its quite easy to determine
what it is...

In addition I have a SKUAttributes table - this holds information about each
of the SKU's...typically when there are listed items for each...perhaps
"Topics", or "Objectives"....so this links back on the SKUID...

Now - here's the good bit!

If you were to examine a package you would find an entry in the SKU table,
you would find one or more entires in the SKUAttributes table with items
called "Members"...these have a value of a SKUID - which, in the SKU table
would then be a "series"...repeat this again and you'd find yourself back in
the SKU table looking at hte courses that belong to the series...

So, I've got most of it worked out and doing what i need...but the problem
I'm having now is getting myself a BIG LIST...

I want to have a recordset returned which has each package, and then the
series for each as subsequent rows...with one column containing the package
id/name or something that I can use to differentiate between when I'm
running this through ASP (so I can begin a new area on the page for the next
package's contents)....

I have achieved a bit of this already by using a UNION ALL, but this was
only when dealing with a single package, because I was able to specific the
SKUID (from the querystring on the webpage) - but what I want now is to get
ALL packages, and ALLseries for those packages....

I'm not great with the DDL/DLL stuff so I've put a little example of the
structure below...

SKU table

SKUID varchar(30)
SKUType varchar(12)
SKUTitle varchar(250)
... there are a load of other columns too but none really relevant at this
time

SKUAttribute table

SKUAttributeID int
SKUAttributeSKUID varchar(30)
SKUAttributeTypeID int
SKUAttribute varchar(250)

Example data...

SKU

pkg.bsvce.h65t     Business Skills Videos: Essentials
pkg
pkg.bsvcm.r8c      Business Skills Videos: Management
pkg
pkg.bsvcp.gs7c     Business Skills Videos: Personal Development     pkg

SKUAttribute

23986     pkg.bsvce.h65t     4     srs.pbc004.2gvv
23987     pkg.bsvce.h65t     4     srs.pbf001.gox8
23988     pkg.bsvce.h65t     4     srs.pbg001.6yaf
23989     pkg.bsvce.h65t     4     srs.bssamk.c2xw
23990     pkg.bsvce.h65t     4     srs.pbw002.ixeo

What I was using for the individual result would be this:

SELECT SKUID, SKUTitle, SKUType, null AS MemberText
FROM SKU
WHERE SKUID = '" & Request.QueryString("skuid") & "' AND SKUType = 'pkg'
UNION ALL "
SELECT null, null, null, a.SKUID
FROM SKU AS a
WHERE a.SKUID IN
( SELECT SKUAttribute FROM SKUAttribute WHERE SKUAttributeTypeID = 4 AND
SKUAttributeSKUID = '" & Request.QueryString("skuid") & "' ) "

This would return me data like this:

pkg.eudc.kr3c     End User Desktop Computing          pkg         NULL
NULL                 NULL                                             NULL
srs.a2kmou.9pvb
NULL                 NULL                                             NULL
srs.axpmou.9geb
NULL                 NULL                                             NULL
srs.bcs2it.9lux
NULL                 NULL                                             NULL
srs.cmb001.d0bo
NULL                 NULL                                             NULL
srs.comcpt.bo1w

So, this was based on passing in a value from the querstring, now I want to
get ALL of hte packages and series like the above but obviously I'll not be
specifying a SKUID....

Can I still use a UNION for this or do I need joins? I think I've just got
out of my league a little bit, so I'm hoping the above examples/info will be
of use to someone who might be able to help me...

Thanks in advance for your time/help..

Rob