Group By Advice - SQL Server Forums

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)

Group By Advice

Author

Topic

johns
Starting Member

24 Posts

Posted - 2003-06-26 : 22:09:11

I have multiple categories with each sub-category pointing to it's parent category (cParentID). The top level categories have no parent category, thus cParentID = 0.

All works great, but if I try to GROUP BY "a.cParentID" so all categories that have the same parents will be grouped together under the parent I get an error message.

SELECT a.cID, a.cName, a.cParentID,
(SELECT b.cName FROM tblCategories b WHERE b.cID = a.cParentID) AS ParentName
FROM tblCategories a
GROUP BY .......
ORDER BY a.cParentID, a.cName

What I am trying to achieve is:

Parent
- child
- child
- child
Parent2
- child
- child
Parent3
- child
etc...

Thanks for any ideas.

John S.

dsdeming

479 Posts

Posted - 2003-06-27 : 08:22:54

What is the error message?

Dennis

johns
Starting Member

24 Posts

Posted - 2003-06-28 : 00:01:54

The Group By is not working for me, nor the is the Order By. Here is an example I put together:

Parent Category ----- Category Name
none..................fruit (cID = 1; cParentID = 0)
none..................juice (cID = 6; cParentID = 0)
none..................veggies (cID = 11; cParentID = 0)
fruit.................apple (cID = 2; cParentID = 1)
fruit.................orange (cID = 3; cParentID = 1)
fruit.................pear (cID = 4; cParentID = 1)
fruit.................picking tools (cID = 5; cParentID = 1)
juice.................apple (cID = 7; cParentID = 6)
juice.................grape (cID = 8; cParentID = 6)
juice.................orange (cID = 9; cParentID = 6)
juice.................tomato (cID = 10; cParentID = 6)
picking tools.........knife (cID = 12; cParentID = 5)
picking tools.........saw (cID = 13; cParentID = 5)

All child categories that are derived from 'fruit' I want to be grouped together. Same thing with 'juice' and 'veggies'. So the result would look like this....

Parent Category ----- Category Name
none..................fruit (cID = 1; cParentID = 0)
fruit.................apple (cID = 2; cParentID = 1)
fruit.................orange (cID = 3; cParentID = 1)
fruit.................pear (cID = 4; cParentID = 1)
fruit.................picking tools (cID = 5; cParentID = 1)
picking tools.........knife (cID = 12; cParentID = 5)
picking tools.........saw (cID = 13; cParentID = 5)
none..................juice (cID = 6; cParentID = 0)
juice.................apple (cID = 7; cParentID = 6)
juice.................grape (cID = 8; cParentID = 6)
juice.................orange (cID = 9; cParentID = 6)
juice.................tomato (cID = 10; cParentID = 6)
none..................veggies (cID = 11; cParentID = 0)

Any help is appreciated.

Thanks,

John S.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources