|
|
 |
 |
 |
 |
SELECT with one-at-a-time view...
Hi, I have a problem I've been stuck in for three days - categories in a datagrid view. My first SELECT is simple: select *
FROM categories
where parent_id = -1 and active = 1
order by sort_order asc
which produces this:
id parent_id name
1239 -1 Battery Related
1264 -1 Brush Guards, Bumpers & Racks
1566 -1 Camouflage Accessories
601 -1 Carpets & Floormats
1241 -1 Custom Accessories
1244 -1 Electric Motors
...etc...
Now I want to loop through a sub category select for each of the above
parent
categories.
I know what the select is to generate my views if I manually did each
one:
SELECT id, name, parent_id
FROM categories
WHERE parent_id = 1239 <<Notice this id column brings the view I need
now as parent_id.
ORDER BY name
generates:
id name parent_id
597 Golf Cart Battery Care 1239
598 Golf Cart Battery Charge Indicators 1239
667 Golf Cart Battery Charger Repair Parts 1239
784 Golf Cart Battery Chargers 1239
1292 Golf Cart Battery Watering Systems 1239
Here is the view I want that I can't figure out and need your help
with:
Battery Related
Golf Cart Battery Care
Battery Books
Battery Terminals and Cables
Battery Tools
Golf Cart Battery Charge Indicators
Golf Cart Battery Charge Repair Parts
Charge Ammeters
Charge Capacitors
Charge Cords and Plugs
AC Cords and Plugs
DC Cords and Plugs
Any help is very much appreciated.
Thanks,
Trint
On May 29, 4:03 pm, trint <trinity.sm @gmail.com> wrote:
> Hi, > I have a problem I've been stuck in for three days - categories in a > datagrid view. > My first SELECT is simple: > select *
> FROM categories
> where parent_id = -1 and active = 1
> order by sort_order asc
> which produces this:
> id parent_id name
> 1239 -1 Battery Related
> 1264 -1 Brush Guards, Bumpers & Racks
> 1566 -1 Camouflage Accessories
> 601 -1 Carpets & Floormats
> 1241 -1 Custom Accessories
> 1244 -1 Electric Motors
> ...etc...
> Now I want to loop through a sub category select for each of the above
> parent
> categories.
> I know what the select is to generate my views if I manually did each
> one:
> SELECT id, name, parent_id
> FROM categories
> WHERE parent_id = 1239 <<Notice this id column brings the view I need
> now as parent_id.
> ORDER BY name
> generates:
> id name parent_id
> 597 Golf Cart Battery Care 1239
> 598 Golf Cart Battery Charge Indicators 1239
> 667 Golf Cart Battery Charger Repair Parts 1239
> 784 Golf Cart Battery Chargers 1239
> 1292 Golf Cart Battery Watering Systems 1239
> Here is the view I want that I can't figure out and need your help
> with:
> Battery Related
> Golf Cart Battery Care
> Battery Books
> Battery Terminals and Cables
> Battery Tools
> Golf Cart Battery Charge Indicators
> Golf Cart Battery Charge Repair Parts
> Charge Ammeters
> Charge Capacitors
> Charge Cords and Plugs
> AC Cords and Plugs
> DC Cords and Plugs
> Any help is very much appreciated.
> Thanks,
> Trint
SELECT a.id, a.name, a.parent_id FROM categories a, categories b WHERE a.parent_id = b.id ORDER BY name -----------------------------------------------Reply-----------------------------------------------
What version of SQL Server are you using? 2005: Look into CTEs (Common Table Expressions);
2000: Take a look at this example:
http://milambda.blogspot.com/2005/07/climbing-trees-is-for-monkeys.html
ML
---
http://milambda.blogspot.com/
-----------------------------------------------Reply-----------------------------------------------
On May 29, 7:30 am, ML <M @discussions.microsoft.com> wrote: Thanks, I will try all of these. Trint -----------------------------------------------Reply-----------------------------------------------
Well, let us know how you get along. ML
---
http://milambda.blogspot.com/
-----------------------------------------------Reply-----------------------------------------------
Here is one way to do this with a recursive CTE (SQL Server 2005): WITH CategoriesTree (id, name, parent_id, ord)
AS
(
SELECT id, name, parent_id,
CAST('-' +
CAST(ROW_NUMBER() OVER(ORDER BY name) AS VARCHAR(8)) +
'-' AS VARCHAR(MAX))
FROM Categories
WHERE parent_id = -1 AND active = 1
UNION ALL
SELECT C.id, C.name, C.parent_id,
CAST(T.ord +
CAST(ROW_NUMBER() OVER(PARTITION BY C.parent_id
ORDER BY C.name) AS VARCHAR(8)) +
'-' AS VARCHAR(MAX))
FROM Categories AS C
INNER JOIN CategoriesTree AS T
ON C.parent_id = T.id
)
SELECT id, name, parent_id
FROM CategoriesTree
ORDER BY ord;
Note: This selects the full tree with all top level categories and the
subcategories underneath. You can add a WHERE condition to filter only a
particular category.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
-----------------------------------------------Reply-----------------------------------------------
On May 29, 10:01 am, "Plamen Ratchev" <Pla @SQLStudio.com> wrote:
> Here is one way to do this with a recursive CTE (SQL Server 2005): > WITH CategoriesTree (id, name, parent_id, ord)
> AS
> (
> SELECT id, name, parent_id,
> CAST('-' +
> CAST(ROW_NUMBER() OVER(ORDER BY name) AS VARCHAR(8)) +
> '-' AS VARCHAR(MAX))
> FROM Categories
> WHERE parent_id = -1 AND active = 1
> UNION ALL
> SELECT C.id, C.name, C.parent_id,
> CAST(T.ord +
> CAST(ROW_NUMBER() OVER(PARTITION BY C.parent_id
> ORDER BY C.name) AS VARCHAR(8)) +
> '-' AS VARCHAR(MAX))
> FROM Categories AS C
> INNER JOIN CategoriesTree AS T
> ON C.parent_id = T.id
> )
> SELECT id, name, parent_id
> FROM CategoriesTree
> ORDER BY ord;
> Note: This selects the full tree with all top level categories and the
> subcategories underneath. You can add a WHERE condition to filter only a
> particular category.
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com
Plamen Ratchev, I tried this query and it is exactly what I needed. Only one thing is the sorting. I may need to ask another question in a moment please. Thanks, Trint -----------------------------------------------Reply-----------------------------------------------
To understand the ordering you can simply add the ord column to the final SELECT, so you can see how it is constructed. Basically at the top level it uses the ROW_NUMBER ordered by the top level name. Then at each level concatenates (with delimiter to preserve the ordering at the levels) that ord column with the value from the parent, adding the ROW_NUMBER at that level for all subcategories for that particular parent. Here is an example of what you may get for the ord column: -1-
-1-1-
-1-1-1-
-1-1-2-
-1-1-3-
-1-2-
-1-3-
-1-3-1-
-1-3-2-
-1-3-3-
-1-3-3-1-
-1-3-3-2-
-1-4-
-1-5-
-2-
-3-
HTH,
Plamen Ratchev
http://www.SQLStudio.com
-----------------------------------------------Reply-----------------------------------------------
Here is a better version (in terms of ordering) to have the correct order when categories at each level exceed 10: WITH CategoriesTree (id, name, parent_id, ord)
AS
(
SELECT id, name, parent_id,
CAST(ROW_NUMBER() OVER(ORDER BY name)
AS VARBINARY(MAX))
FROM Categories
WHERE parent_id = -1 AND active = 1
UNION ALL
SELECT C.id, C.name, C.parent_id,
T.ord +
CAST(ROW_NUMBER() OVER(PARTITION BY C.parent_id
ORDER BY C.name) AS BINARY(4))
FROM Categories AS C
INNER JOIN CategoriesTree AS T
ON C.parent_id = T.id
)
SELECT id, name, parent_id
FROM CategoriesTree
ORDER BY ord;
The difference here is just casting to BINARY which preserves the ordering
at each level. Again, to understand better what happens just include the ord
column in the final SELECT.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc