We help IT Professionals succeed at work.

Need more help with a SQL query

saabStory
saabStory asked
on
I'm not sure if what I'm trying to do is even possilbe.  I have two tables - one for top level course categories and the other with course levels and course names.  I'm trying to figure out if I can generate a list from SQL that first lists the top level category, then the program levels within that category and then the course names within each program level.

The schema for the two tables is below and I could break things up into 3 tables if need be.  I am trying to get the output to do something like:

Category 1
  Program Level 1
    Course 1-1-1
    Course 1-1-2
    Course 101-3
  Program Level 2
    Course 1-2-1
    Course 1-2-2

Category 2
  Program Level 1
    Course 2-1-1
  Program Level 2
    Course 2-2-1
    Course 2-2-2

And so forth..  The number of program levels and courses varies.  Some Categories have 5 or 6 Program Levels while others only have 1.  Ditto for the Courses - some Program Levels might have 12 Courses while others only have 1 or 2.

As always, thanks in advance for any help on this.
tbl_Categories
====================================
catID	  int (pk)
catName	  nvarchar(50)
catOrder  int


tbl_ProgramLevels
====================================
pID	      int
catID	      int
progLevel     nvarchar(50)
progCourse    nvarchar(50)

Open in new window

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
do you want that output with only SQL?

hierarchical queries are explained here:
http://msdn.microsoft.com/en-us/library/ms186243.aspx

Commented:
Sounds like a result SQL Reporting is made for. Not that the CTE-way angellll suggests is not an option.

Author

Commented:
I'd like it in SQL if possible.  I'm using it to populate a web page within a particularly unforgiving CMS.  Normally this sort of thing is a snap but within the CMS we have, you can't do anything without a module specific to that CMS.  I have a module that works pretty good with SQL queries and stored procedures and I use it to create data driven content like what I'm trying to do here.  

In the end I generate a lot of HTML based off the SQL but need the SQL first.  I can always hard code what I need but the maintenance is a nightmare with the amount of program levels and classes as I'm dealing with.  It's much more manageable to drop the bits into tables and pull from there.

That said, the CTE example looks promising - or maybe nested sets in a stored proc.  It may also be the case that this just won't work for what I need.  
Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
If you're output is just a single character column, you could do that with standard SQL easily enough.

What order should the program levels be in?  pID order or progLevel (name) order?  I can see potential pitfalls with either but don't see a third option :-) .

Author

Commented:
The output would be just like:

Category 1
  Program Level 1
    Course 1-1-1
    Course 1-1-2
    Course 101-3
  Program Level 2
    Course 1-2-1
    Course 1-2-2

Category 2
  Program Level 1
    Course 2-1-1
  Program Level 2
    Course 2-2-1
    Course 2-2-2

etc..

Currently the Categories are in one table and the program levels and courses in another - though I need to break out the program levels.  Like I said - this may be more trouble than it's worth.  I can certainly do it in HTML but maintaining several hundred courses and the attendant code would be a nightmare.  As a next-to-last resort, I can always load as many instances of the CMS module as there are categories and run uber simple individual queries - but that offends the part of me that would like to see an all in one solution. Last resort would be HTML...
Senior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:

-- Get all categories
SELECT c.catOrder, '' AS progLevel, 0 AS pID, c.catName AS Name
FROM dbo.tbl_Categories c
--WHERE ...

UNION ALL

-- Get all progLevels
SELECT c.catOrder, p.progLevel, 0, SPACE(2) + p.progLevel AS name
FROM dbo.tbl_ProgramLevels p
INNER JOIN dbo.tbl_Categories c ON
    c.catID = p.catID
--WHERE ...

UNION ALL

-- Get all progCourses
SELECT c.catOrder, p.progLevel, p.pID, SPACE(4) + progCourse AS name
FROM dbo.tbl_ProgramLevels p
INNER JOIN dbo.tbl_Categories c ON
    c.catID = p.catID
   
-- Put output in correct order
ORDER BY
    catOrder, progLevel, pID

Author

Commented:
Well that is awesome enough.  I'll have do some output tweaking but this is what I need.  Many thanks!

Author

Commented:
Superb as always.
Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Thanks :-).

Yeah, I should've used an outer query for the final result:

SELECT name
FROM (
...other SELECTs as above (give or take) minus the ORDER BY
UNION ALL
SELECT...
UNION ALL
SELECT ...
) AS derived
ORDER BY catOrder, progLevel, pID


Yeah, as I said, not exactly sure how to properly sort/order by the progLevel and progCourse, but you can probably tweak that.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.