Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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:

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:

> 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/

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:

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