Help with an SQL Statement -- sql server programming

Nigel Rivett
9/18/2004 2:51:01 PM

select CategoryId, SubCategoryID = 0, name = Categoryname
from Category
union all
select CategoryId, SubCategoryID, name = SubCategoryname
from SubCategory
order by CategoryId, SubCategoryID

[quoted text, click to view]

Joe Celko
9/18/2004 6:22:09 PM

I have a new book on trees and hierarchies in SQL that has several
relational solutions to this problem. Until you can buy the book
(hint,hint), look up the "nested sets model" posting in the newsgroup.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

*** Sent via Developersdex http://www.developersdex.com ***

Reapes
9/18/2004 9:07:22 PM

Hi Group,

I have two tables in SQL Server.

Table one is called Category with fields :

CategoryID and CategoryName.

Table two is called SubCategory with fields:

SubCategoryID
CategoryID
SubCategoryName

In Table one I have say 2 categories, Birthday and Easter.
In Table two I have 2 sub categories of Male and Female which are sub
categories of Table ones Birthday entry.

What I would Like to return (if possible) using an SQL statement is the
following:

Birthday
Male
Female
Easter

i.e. first the MAIN category, then the sub categories for that main category
then onto the next category etc etc.

All the queries I have tried failed miserably! Is there any sql gurus out
there that can point me in the right direction?

Many Thanks

Reapes

Reapes
9/18/2004 11:26:20 PM

Many many many thanks Nigel, you are truly a genius!

It works like a charm.

Reapes

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Help with an SQL Statement