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
ADVERTISEMENT
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
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
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
Jul 20, 2005
Hi,Have a database that contains various tables.I need to run a query on two tables.Table A contains a column called TitlesTable B contains a column called Uni_TitlesIn Table B's column it contains multiple titles as shown belowseparated by semi-colons in each row.Table B Uni-Title Column__________________________Row 1: Landlord and tenant; Leases; Rent reviewsRow 2: Acquisitions; Advisers; Appointment; Contract termTable A - Titles Column_________________________Table A's column contains a only one of the words in Table B's columni.e. Landlord and tenantI would like to obtain a count of how many times the same word orphrases appears in the column of Table B.ThanksSteve
View 2 Replies
View Related
Mar 10, 2015
selecting table data in hierarchical XML .
Here is the sample table DDL and data
Declare @continents Table
(
id int identity (1,1)
,continent_id int
,continent_Name varchar(100)
,continent_surface_area varchar(100)
,country_id int
[code]....
View 8 Replies
View Related
Feb 29, 2008
hii,,i am using asp.net 2005 and sql server 2005.i have a web page in which i can enter details and it gets stored in a table in a database..in the table thrs a column called as sme_id,,what i want is when one inserts a new sme_id from the page,,it should check in the table so tht no duplicate sme_id wil b generated..,,this code is workin fine,,i just want to implement the above condition...here is the insert code which i have used along with sql datasource:::__________________________
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:sme_trackerConnectionString %>"
InsertCommand="INSERT INTO SME_Master(SME_Id, FirstName, LastName, Type_of_SME, Agency_id, Agency_Name, Email, Address, Phone, Mobile, Fax, TimeZone_Id, Experience, City, State, Status, Level_Of_Exam, Other_Comments, Certificate, Expertise)
VALUES (@SME_Id, @FirstName, @LastName, @Type_of_SME, @Agency_id, @Agency_Name, @Email, @Address, @Phone, @Mobile, @Fax, @TimeZone_Id, @Experience, @City, @State, @Status, @Level_Of_Exam, @Other_Comments, @Certificate, @Expertise)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [SME_Master]">
_______hope u got my problem,,,,,reply asap....thnks in advance
View 11 Replies
View Related
Apr 14, 2007
I have a single table named PROCESS which contain following three fields
ProcessID
ParentID,
info
* Every process have a unique ProcessID and ave single parent process which is identified by ParentID.
* If a process does not have a Parent then its ParentID value is -1.
*Only single level of Parent-child hierarchy is maintained.
Can anyone please tell me the Query that uses Recursive JOINS to retrieve the ProcessID s based in following Conditions.
1. if the Parent's 'info' field contains given value then retrieve all the process under it.
2. Retrive all the process whose 'info' contains given value and excluding the Processes resulted from 1st conditiion
Thanks in advance
Bharath Booshan L
View 2 Replies
View Related
Jul 20, 2005
Hi,I am trying to concoct a query that will join rows on the basis of awhitespace insensitive comparison. For instance if one row has the value'a<space>b' and another has the value 'a<space><space>b' I want them to beconsidered equal (but not the same as 'ab')I am happy to do some T-SQL if that helps. Also I have a full-text index onthe column in question, but note that I am comparing rows against eachother, not to a fixed string.This is for a one-off job, so if there is no obvious way to do it on thedatabase, I will just bcp out the data and knock up some perl or somethingto do it. All other things being equal I would rather do it on the databasethough.Many thanksAndy
View 11 Replies
View Related
Sep 6, 2006
Hello,
Can someone explain why this code contains the following error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "TheTable.StartValue" could not be bound.
CREATE FUNCTION MyFunction(@StartValue int)
RETURNS @MyTable TABLE
(
NextValue int NOT NULL
)
AS
BEGIN
INSERT INTO @MyTable(NextValue)
VALUES (@StartValue + 1)
INSERT INTO @MyTable(NextValue)
VALUES (@StartValue + 2)
RETURN
END
GO
CREATE TABLE TheTable
(
StartValue int NOT NULL
)
GO
INSERT INTO TheTable(StartValue)
VALUES (10)
INSERT INTO TheTable(StartValue)
VALUES (20)
GO
SELECT *
FROM TheTable CROSS JOIN
MyFunction(TheTable.StartValue)
View 6 Replies
View Related
Apr 26, 2015
I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?
Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.
View 3 Replies
View Related
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
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
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
May 3, 2007
Hellofor MS SQL 2000 i am having :CREATE TABLE [dbo].[Items]([id_Items] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,[id_ItemsSup] [int] NULL,[Name] [nvarchar] (100) NOT NULL,[SubItems][int] DEFAULT (0)) ON [PRIMARY]with : UPDATE [Items] SET SubItems = (SELECT COUNT(id_Items) AS ct FROM dbo.Items WHERE id_ItemsSup = 1) WHERE id_Items = 1I get how many subItems has Item = 1how can I update the Column SubItems (for each row) ?to get the total of subItems for each Item ?thank you
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
