MS SQL Server :: Hierarchical Table (count)

Hierarchical Table Functions Vs Hierarchical CTE

Jun 9, 2006

Recently I was in need of a hierarchical tree data. I learned about CTE and how they can be used to build hierarchical data with simple syntax. I used CTE and was through with the task. Later during free time, I tried to compare CTE approach with the traditional SQL 2K Table Function approach. It was surprising to see the query costs when I ran both the modes at one go...

Query Cost (relative to batch) : 0.49%
Query Text : Select * From fn_GetTree(8);

Query Cost (relative to batch) : 99.51%
Query Text : with treedata (id, parentid, status, prevStatus, lvl) as (select ...)

What does that indicate? Does it mean that the Table Function approach is much faster than CTE? I am sure that I was not making unwanted Joins in the CTE mode.

Can someone explain why that huge difference is there? And what the scenarios where CTE is better over Table Functions?

View 8 Replies View Related

On Delete Cascade && Hierarchical Table

Mar 24, 2007

for MS SQL 2000
I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE

CREATE TABLE [dbo].[Users](
[id_Users] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_UsersSup] [int] NULL,
[Users] [nvarchar] (100) NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [FK_Users_Sup] FOREIGN KEY
(
[id_UsersSup]
) REFERENCES [Users] (
[id_Users]
)
ON DELETE CASCADE

but MS SQL refuse to create the foreign key
even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under

thank you for helping

View 2 Replies View Related

Database Table - Hierarchical Query

Aug 29, 2014

Following is my db table

student_id student_code student_parent_id student_name
1 11 0 a
2 111 1 b
3 1111 2 c
4 11111 3 d

How to generate following op?

student_id student_code student_parent_id student_name Hierarchy
1 11 0 a 11 - 111
2 111 1 b 11-111-1111
3 1111 2 c 11-111-1111-11111
4 11111 3 d 11111

View 9 Replies View Related

Hierarchical Table - How To Get Data From Users

Nov 30, 2013

I have two table. Department is hierarchical table.

Department
--- id (int primary key)
--- name (varchar)
--- parent (int)

Users
--- Id
--- name
--- department_id

This query return all data from departments. But i cannot understand how get data from users

SELECT t1.name AS lvl1, t2.name as lvl2, t3.name as lvl3
FROM Department AS t1
LEFT JOIN Department AS t2 ON t2.parent = t1.id
LEFT JOIN Department AS t3 ON t3.parent = t2.id

View 1 Replies View Related

Transact SQL :: Joining To A Hierarchical Table

Oct 19, 2015

I have a lookup table with 4 levels of codes like follows:

create table #RiskElementCategory(
[RiskElementCategoryCode] [nchar](5),
[RiskElementCategoryCodeDsc [nvarchar](50),
[RiskElementCategoryCode_2] [nchar](5),
[RiskElementCategoryLevel2Dsc] [nvarchar](50),

[Code] ...

Along with some other rows with the same format. I need to join to this table using a RiskElementCode that I get from the Source system. The trick is that it can be at any level, but I don't know which level it is at. So what I have to do is somehow get the correct row from the lookup table based on the code from the source to get the correct level.

So for Example, If i receive the RiskElementCode of 'SSR', that is in column RiskElementCategoryCode_3 so I need the row that has 'NA' for anything after RiskElementCategoryCode_3 where RiskElementCategoryCode_3 = 'SSR'. If i get 'DFR' I need to get the row where RiskElementCategoryCode_4 = 'DFR' since there are no levels deeper than 4 i don't need to check anything else. If I get 'PRR', then I need the row where RiskElementCategoryCode = 'PRR' and code_2, code_3 and code_4 = 'NA'.

So besides getting the correct row based on the code, i need to get the correct row based on the level where the next levels are 'NA'. I should only get 1 row each time.

View 2 Replies View Related

Retrieving Hierarchical Data From A Single Table

Sep 3, 2006

I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure. Example data:ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level------------------------------------------------------------------------------------------------------------------------------------------------1 Custom Furniture 0 02 Boxes 0 03 Toys 0 04 Bedroom 1 Custom Furniture 15 Dining 1 Custom Furniture 16 Accessories 1 Custom Furniture 17 Picture Frames 6 Accessories 28 Serving Trays 6 Accessories 29 Entertainment 1 Custom Furniture 110 Planes 3 Toys 111 Trains 3 Toys 112 Boats 3 Toys 113 Automobiles 3 Toys 114 Jewelry 2 Boxes 115 Keepsake 2 Boxes 116 Specialty 2 Boxes 1Desired output:Custom Furniture Accessories Picture Frames Serving Trays Bedroom Dining EntertainmentBoxes Jewelry Keepsake SpecialtyToys Automobiles Boats Planes Trains

View 4 Replies View Related

SQL Server 2012 :: Building Self-referencing Hierarchical Table

May 21, 2014

An example of what I am talking about is the employee table in the Adventureworks database. This has employeeID and then ManagerID, ManagerID just being the EmployeeID of the person whom the original reports to.

I know the queries for querying this type of data and even making recursive common table expressions. What I cannot seem to find is how one goes about BUILDING said table. I see all sorts of examples where people are just doing INSERT table VALUES () manually to load the table. The problem is, I need to create a table that has potentially thousands of records.

It will essentially be a dimensional map. Don't even get me started as to they why, I will just suffice to say that is what the client and project want . I have a process that will do this now, but it is not very dynamic and very hard coded. To me, there seems like there should be some sort of standardized methodology for handling this.

View 9 Replies View Related

Table Row Count + Index Row Count

Jul 23, 2005

SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.

View 5 Replies View Related

Inserted Rows Count From SSIS Not Like Table Rows Count

Jun 25, 2007

Hi all

i using lookup error output to insert rows into table

Rows count rows has been inserted on the table 59,123,019 mill

table rows count 6,878,110 mill ............................

any ideas

View 6 Replies View Related

Count All Table Rows Then Insert Into Test Table Using SSIS Packages

Jul 15, 2013

I have database test007DB and I need count all table rows then insert into test99 table using ssis packages .

test99: tableName countRows
t1 20
t2 30
t3 25

View 2 Replies View Related

Capturing Record Count For A Table In Oracle And Saving It In A Table In SQL Server

Jun 11, 2007

I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.

I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:

Imports System.Data.SqlClient

Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.

any idea?

thanks

View 16 Replies View Related

SQL Call To Count The Total Rows In Table B For Each User In Table A

Jan 17, 2006

I have 2 tables:

TableA:
Name
UserA
UserB
UserC

Table B:
Name               Data
UserA              xxx
UserB              asdasd
UserB              ewrsad
UserC              dsafasc
UserA              sdf
UserB              dfvr4

I want to count the total entries in Table B for every user in Table A. The output would be:

Name               Count
UserA              2
UserB              3
UserC              1

I can use a Select Count statement, but I will have to make a SQL call for every user in Table A. Also, Table A is dynamic, so the users are always changing. Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?

View 5 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

Hierarchical Design

Mar 27, 2008

I am designing database that will store clinic and doctor information.

1) A clinic can have doctors and staff members.
2) A clinic can belong to another clinic.
3) A doctor can practice on his/her own practice/clinic and still belong to another clinic.

I will email my current design if needed.

Thanks,

Stephen Cantoria
scantoria@msn.com

View 3 Replies View Related

Hierarchical XML Import?

Apr 21, 2006

Hello,

Can anyone point me at a tutorial or sample that shows how to use IS for importing an xml file containing hierarchically arranged records ?

I have a file which contains multiple orders , the orders contain multiple line items.. the file also contains an element with details of the file source etc...

So, I want to make an insert in the FileLog table an then make inserts into the orders table .. then make inserts into the OrderItems table which will have the foreign key from the orders table in the records...

if you get what I mean...

But I have searched hign and low and can't see any info on how to load anything but a very flat xml file structure...

Thanks

Vida.

View 9 Replies View Related

Hierarchical Cumulative Sum

Aug 23, 2006

I have a table consisting of 3 columns: Parent varchar(50), Child varchar(50), Pop int.

The table is setup as follows:

Parent Child Pop
----------------------------------
Europe France 0
France Paris 1
New York New York City 10
North America United States 0
North America Canada 0
United States New York 0
United States Washington 0
Washington Redmond 200
Washington Seattle 100
World Europe 0
World North America 0

This is just some sample data modified a tiny bit from an example of a hierachical print out sample that is a stored procedure that allows me to pass any place and see all of that place's children/grandchildren.

I need to figure out how to write a query to show me cumulative sums (ROLLUP?) of the whole tree. So the output should basically be something like this (it can include parent and child columns too):

World Null 311
World Europe 1
Europe France 1
France Paris 1
World North America 310
North America United States 310
North America Canada 0
United States New York 10
United States Washington 300
New York New York City 10
Washington Redmond 200
Washington Seattle 100

Hopefully you understand what i'm looking for. I've tried using WITH ROLLUP and I also tried using an Inner Join but I'm not really sure what I need to do to pull this off. I seem to only be able to get it to work 1-2 levels deep but not through the whole tree.

Any help/ideas would be appreciated! Thank you.

View 13 Replies View Related

Performing A Hierarchical Query...

Mar 26, 2008

Hi. I'm trying to find out which "cases" have a new items added to our database. I have provided a sample layout.

ID ParentID Name CreateDate
358 2 SMITH, JOHN 3/3/2008 11:15:23 am
359 358 Invoice 3/5/2008 4:13:52 pm
360 358 Shipping 3/5/2008 5:11:09 pm
361 358 Receiving 3/6/2008 4:22:01 am

The main ID for this is 358. The invoice, shipping, and receiving items are child items. I would like to run a query that can report which cases have newly added items. This is hierarchical I guess and I'm quite lost. I hope this makes sense. Thanks for any help!

View 3 Replies View Related

Hierarchical Query From Two Tables

Nov 17, 2014

I have created a store procedure as below:

WITH TextType AS
(
SELECT AppTxtTypeId,AppTxtTypeCode, AppTxtTypeParentCode, Name,Description,Active,SortOrder ,0 as TypeLevel,AppTxtTypeId as parentId
FROM [ApplicationTextTypes]
WHERE AppTxtTypeParentCode IS NULL

[Code] ....

From this i am able to get data in the below format:

Parent
--Child1
--Child2
---Subchild1-Child1
---Subchild2-Child1
---Subchild1-Child2
---Subchild2-Child2

Actually my requirement is :

Parent
--Child1
---Subchild1-Child1
---Subchild2-Child1

--Child2
---Subchild1-Child2
---Subchild2-Child2

View 1 Replies View Related

Fabricated Hierarchical Recordset

Jul 20, 2005

I want to use fabricated hierarchical recordset in VB6 using ADO. I wrotecode likedim rs as adodb.recordestset rs=new adodb.recordestrs.fields.append "a1",adChar,30Then in loop I putrs.addnewrs("a1")=...re.updatewhen I associated this with hierarchical flexgrid I saw what I expected. Onthe next step I added liners.fields.append "a2",adChapterand this operator gave me error that I use wrong parms. Then I realized thatI should use specific connection. But with this connection adChar stopped towork also. Is it possible to resolve this problem?--Aleks Kleynhttp://www.geocities.com/aleks_kleyn

View 1 Replies View Related

Hierarchical Data Model

Sep 28, 2007

Hi,

I would like to know best way to design the database for the following requirement. I have a collection of tree nodes. each node has a type and set of attributes and a parent node (except for the node which has no parent). node type refers to the level of the node in the tree. child node inherits the attributes from the parent node (similar to object oriented programming where derived class inherits properties of the base class). user can add/update/delete nodes from the tree and user can choose to override the attributes of the parent node in child node.
what is best way to store this type of data? should there be a separate table for each node type (level in the tree). but the problem with this approach is that we need to duplicate the columns of the parent node, because user can overwrite the parent node attributes in the child node. there can be more than one at the same level and all of them share same set of attributes. this concept is exactly like inheritance in object oriented programming. as far as the data is concerned, there are around 15 levels, around 30K nodes and 30 attributes spread across different node levels.

thanks,
RK

View 1 Replies View Related

Hierarchical Data In Result Set

May 1, 2006

How can I create a function that returns hierarchical data from a table with this structure:

- CategoryID
- CategoryName
- CategoryFather

I want to bring the result set like this...

CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2

How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.

Thanks.

View 5 Replies View Related

Hierarchical Resultset Sorting

May 25, 2006

I have a query like this

with TempCTE(id, Name, level, sortcol)
As
(
Select id, Name, 0 as level,
cast(cast( id AS BINARY(4)) as varbinary(100)) sortcol
from Table1
where id = 1

union all

Select id, Name, 0 as level,
cast(sortcol + cast( id AS BINARY(4)) as varbinary(100)) sortcol

from Table1 inner join TempCTE on TempCTE.id = Table1.parentid
)

select * from TempCTE order by sortcol

My problem is I want to sort this hierarchical resultset further on name like

aaa
-----aaaa
-----bbbb
-----cccc
------------aaaaa
------------bbbbb
-----dddd
------------aaaaa
------------bbbbb
bbb
-----aaaa
-----bbbb

Thanks

View 1 Replies View Related

Flatten Hierarchical Data

Apr 17, 2008

Hello

I have a problem that I am hoping somebody can help me with!

I have built a hierarchy using the adjacency list model so I have records with an id that maps to the parent record so my hierarchy looks something like this:-

Newspapers

National Newspapers

Daily Express

Express Publications
Express Supplements
Daily Mail

Mail Publications
Mail Supplements
Mirror

So my table would look like below:-

1 Newspapers Null
2 National Newspapers 1
3 Daily Express 2
4 Express Publications 3
5 Express Supplements 3

and so on. What I would like to be able to do is flatten out the hierarchy so I get something like below where each level is in a column.

NewsPapers National Newspapers Daily Express Express Publications
NewsPapers National Newspapers Daily Express Express Supplements

Ive used CTE's for displaying the hierarchy and producing aggregate figures when joing the hierarchy to spend information but am struggling to come up with any code that would produce a flattened hierarchy.

Any help would be greatly received!

Thanks

Rich

View 5 Replies View Related

Import Hierarchical Data

Feb 17, 2006

hi folks,

I have to import hierarchical text files like:
32;country;city;postalcode;street
21;name;firstname;salutation;title;age;nickname
21;name;firstname;salutation;title;age;nickname
...

additionally I have to eleminate doubles. what is the best way for this problem ?
I have set up a flatfilesource with two columns and a conditional split on the first column
so now I have an output with [country;city;postalcode;street] and one with [name;firstname;salutation;title;age;nickname]. How do I split this in columns, put it in a dataset keeping the relations and remove doubles ?

Iam looking forward for any helping idea.

rgrds,
matze

View 11 Replies View Related

Storing Hierarchical In A Relational Database

Dec 27, 2005

What is the best approach for storing hierarchical
data in a database? For example, if I need to store a tree menu system,
how would I do that allowing for the most normalization within the
database, using the least number of queries/resources when pulling the
data out, and using the least amount of overhead both in storage and
retrival?

-Chris

View 3 Replies View Related

T-SQL (SS2K8) :: Mapping Hierarchical Relationships

Dec 31, 2014

I am trying to build something which allows different user groups to inherit properties from other groups. For instance, a given user group (say 5) would inherit the universe definition of another user group (say 4). The catch is that this might in turn inherit its universe from another user group (say 3, and so on and so forth).

What I want is a way to give the system a user group and return the actual user group from which it inherits.

So extending my previous example above, suppose the following structure of inheritance:

5 <= 4 (5 inherits from 4)
4 <= 3
3 <= 2
2 <= 1
∴ 5 <= 1 (5 inherits from 1)

I feel like there’s a way to do this with an rCTE but the specifics are eluding me. Here’s some sample code

if object_id('tempdb.dbo.#Inherit') is not null drop table #Inherit
create table #Inherit
(
UserGroupId int,
Property varchar(30) check (Property in ('Universe', 'EventInstances', 'Scores')),

[Code] ....

View 4 Replies View Related

Node ID - Loading Hierarchical Data

Aug 3, 2013

CREATE TABLE #Source
(
Id int identity(1,1)
,categoryint
,Leaf_Node_code varchar(10) --
,Level1_Name varchar(20)
,Level2_Name varchar(20)

[Code] ....

Here category 1 has 3 levels ,

category 2 has 4 levels ,
category 3 has 5 levels ,

Below is the target table, here Leaf_Node_code should populate to only for leaf nodes for each category .. Need to populate Node_id with hierarchical data

I am unable frame a sql query to handle different levels , in future #Source may have more levels .

How to handle multiple hierarchy levels .. here only leaf node should have Leaf_Node_code

CREATE TABLE TARGET_TABLE
(
ID INT IDENTITY(1,1) primary key
,Node_id HIERARCHYID
,category int
,Parent_id int references TARGET_TABLE(id)
,Leaf_Node_code varchar(10)
,Namevarchar(20)
)

Here is the expected output:

IDcategoryParent_idLeaf_Node_codeName Node_id
11NULLNULLWorld
211NULLAsia
312101India
42NULLNULLa
524NULLaa
625NULLaaa
726102aaaa
83NULLNULLb
938NULLbb
1039NULLbbb
11310NULLbbbb
12311103bbbb

View 1 Replies View Related

Hierarchical Selection Within A Select Statment

Jun 7, 2007

CREATE TABLE RS_A(ColA char(1), ColB varchar(10))INSERT INTO RS_AVALUES ('S', 'shakespeare')INSERT INTO RS_AVALUES ('B', 'shakespeare')INSERT INTO RS_AVALUES ('P', 'shakespeare')INSERT INTO RS_AVALUES ('S', 'milton')INSERT INTO RS_AVALUES ('P', 'milton')INSERT INTO RS_AVALUES ('B', 'shelley')INSERT INTO RS_AVALUES ('B', 'kafka')INSERT INTO RS_AVALUES ('S', 'kafka')INSERT INTO RS_AVALUES ('P', 'tennyson')SELECT * FROM RS_ANow i need a select which selects based on hierarchyif ColA = 'S', then select only that rowelse if ColA = 'B' then select only that rowelse if colA = 'P' then select only that rowSo my results should look likeS shakespeareS miltonB shelleyS kafkaP tennysonIs there a way to do this within a select statementI tried using a CASE in WHERE CLAUSE but it put out all rows whichexisted/If any of you can help me with this right away, its is greatlyappreciatedThanks in advance

View 2 Replies View Related

Implementing Inheritance For Hierarchical Data

Oct 12, 2007

Hi,
in my application, the data is in hierarchical format. there is a tree with set of nodes having parent child relationships. this data can be stored either through adjacency or nested set model approach. this is fine. but the issue here is that each child node inherits the properties of its parent node, parent's parent node and so on until the root node. lets say root node has two attributes A1 and A2 and they are stored in two columns in a table. but its child nodes inherits this data from its parent and it has its own extra attributes. so should I copy parent's data for the child node as two additional columns? the problem is that there are around 15 levels in the tree and the attribute list grows from top to bottom in the tree. lets say I need to find all the attributes for a leaf node in the tree (both direct and inherited), if I am not storing the inherited attributes for each node, then I need to walk-up the tree and find all the inherited attributes. there are around 30K nodes and each node has around ten attributes. xml is not option because of large volumes of data and auditing and reporting on individual nodes. what is the best way to store this type of data? my current approach is to have an attribute table having nodeid as a foreign key and only store the direct and NOT the inherited attributes of the node in the table, but this means to find all the attributes for the node, I need to gather the attributes of all the parents until the root node. I can't see any easy way out for this.

View 4 Replies View Related

Counting Items In A Hierarchical Category And Subcategories

Mar 30, 2008

I have two tables, one is a list of categories, the other a list of items listed in the categories. The category table is self-referencing through a ParentID column. Top-level categories have ParentID = 0.
Categories========ID intParentID intCatName varchar(30)Items====IDCategoryIDItemID
There is a third table that links to items through ItemID, but this is not important for this problem!
What I want to do is create a stored SQL procedure. This procedure simply pulls rows from the category table and counts the number of items that are in it. This is straighforward enough using COUNT as a "virtual column", but the difficulty is counting the items in the category but also the items in any subcategories.
An end result with two top-level categories and three subcategories in each might look like:
- Category 1 (20)---- Subcategory 1.1 (10)---- Subcategory 1.2 (5)---- Subcategory 1.3 (5)- Category 2 (14)---- Subcategory 2.1 (3)---- Subcategory 2.2 (4)---- Subcategory 2.3 (7)Hence the difficult bit is getting the total number in brackets for each category with subcategories.
What I have at the moment is:
SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE Items.CategoryID = Categories.ID) AS ItemTotalFROM Categories
What I would like is something along the lines of (pseudocode):
SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE <Item is in category or subcategory>) AS ItemTotalFROM Categories
I don't particularly want to use temporary tables and I definitely do not want to do any of this retrieval in my application - it needs to come straight from the database.
Thanks!

View 2 Replies View Related

Hierarchical Tree Problem - CONNECT BY Equivalent

Jan 28, 2005

I use hieararchical tree with parent id.
I want to select all records that are children of one record.

Oracle has Connect by statment, but sql server doesn't provide this.
Does enyone know a sql script for this problem?

View 1 Replies View Related

Path Betwwen Two Nodes (Hierarchical Queries)

Feb 11, 2008

I need sql statement in sql server to print path between any of the two nodes.All node Elements and their respective parents are saved in a table.
Thanks in advance.

When you have nothing to Loose, You'll get everything you want

View 1 Replies View Related