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.
| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2003-09-26 : 18:51:09
|
Hi, I have a table of categories. Categories can have sub-categories.A sub-category references its parent using a field called 'parentID'.I want to be able to provide a category ID and return all children of that ID. This is easy...SELECT * FROM categories WHERE parentID = @catID; I also would like to know whether the categories returned by this query have, themselves, any sub-categories.Something like this but which works....SELECT *, hasSubs = (SELECT Count(*) FROM categories WHERE parentID = categoryID ) FROM categories WHERE parentID = @catID; How can I get the desired result?Cheers,X. |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2003-09-26 : 19:05:23
|
This works...SELECT DISTINCT c1.categoryID, c1.categoryName, c1.catStatID, c1.ordering, c2.parentID FROM categories c1LEFT OUTER JOIN categories c2 ON c1.categoryID = c2.parentIDWHERE c1.parentID = @catID ORDER BY c1.ordering I just have to check whether 'parentID' is null to determine if there are sub-categories.Can anyone do better? |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-09-27 : 02:25:00
|
| So far, you have only asked WHETHER they have sub-categories, but you're hovering right on the edge of needing to display them or do something else, and then additional layers, and so on... which is what is called a hierarchy, or a tree. So I wanted to jump in early and suggest this great article:More Trees & Hierarchies in SQL by Rob Volk------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2003-09-27 : 10:11:21
|
| Hi there,I've read that article. I'd be more confident about using that design if Part 2 existed.X. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-09-27 : 13:58:52
|
| Hmmm... Well, I don't know what to tell you about that. Are there any particular questions or concerns you had about the approach? The author still visits SQLTeam regularly, and I'm sure he'd be happy to jump in with comments/answers. There are also a number of articles available online that talk about the same sort of approach that you have already taken. Mostly, I just wanted to raise the topic and give you a couple of keywords you could use in searches.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
|
|
|
|
|
Using Count() for every record returned