SQL Server 2012 :: Subtotal In Recursive Query

Feb 17, 2014

i've following data in a recursive query:

Par_IdIDABCDEFLevel
Null022646000100022646
02264602242632000002264622426
02264602253232100002264622532
02264602254082000002264622540
02264602255751100002264622557

[code]....

in few words i need subtotal only for who have children.I tried with rollup but i wasn't able to have it similar to the aspected.I put it the level just to let clearer the dependencies.

View 6 Replies


ADVERTISEMENT

SQL Server 2012 :: Lowest Child In Recursive Query?

Jan 13, 2015

I have following input:

CREATE TABLE #tree
(
Childid varchar(20),
Parentid varchar(20)
)
INSERT INTO #tree
(Childid,ParentId)
SELECT '123' , null UNION ALL
SELECT '456' , '123' UNION ALL
SELECT '789' , '456' UNION ALL
SELECT '870' , '456' UNION ALL
SELECT '985' , '870';

Input:

Child IDParent ID
123 NULL
456 123
789 456
870 456
985 870

I am trying to populate lowest level child with path and depth...Output should be:

Child IDParent IDLast ChildPath Depth
123 NULL 789/123 1
456 123 789/123/456 2
789 456 789/123/456/789 3
123 NULL 985 /123 1
456 123 985/123/456 2
870 456 985/123/456/870 3
985 870 985/123/456/870/9854

View 9 Replies View Related

SQL Server 2012 :: Using Recursive Query To Find Path Between Assembly And Parts?

Jul 2, 2014

I'm trying to use a recursive query to find path between assembly and parts.

The BOM is similar to(I've limited the number of rows and lines):

BOM NumberMat Number
20000222001770
20000222003496
20000222001527
20000222003495
20002462002005
20005062000246

[code]....

How should I modify it so that is returns the 4 path?

View 1 Replies View Related

Transact SQL :: Types Don't Match Between Anchor And Recursive Part In Column ParentID Of Recursive Query

Aug 25, 2015

Msg 240, Level 16, State 1, Line 14

Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,

DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),

[code]....

View 2 Replies View Related

SQL Server 2012 :: Many To Many Recursive CTE

Feb 17, 2014

I have the following table:-

CREATE TABLE [dbo].[TransactionComponents](
[pkTransactionComponent] [int] IDENTITY(1,1) NOT NULL,
[pkTransactionID] [int] NOT NULL,
[ComponentID] [int] NULL
) ON [PRIMARY]

With the following data:-

INSERT [dbo].[TransactionComponents]([pkTransactionID], [ComponentID])
SELECT 1,5
UNION SELECT 1,6
UNION SELECT 1,7
UNION SELECT 1,8

[Code] ....

pkTransactionID and ComponentID both link to the same column on another table this enables a many to many relationship, what I need to figure out is a complete tree of relationships from one of the ID's in it. I think I need to write a recursive CTE to achieve this but I am not entirely sure how to write it. Below is my attempt:-

DECLARE @ID INT
SET @ID = 1;
WITH
cteTxHeirachy (TxID, RelTxID, TxLevel)

[Code] ...

This returns:-

15
16
17
18
19
110

But the following are missing:-

10 2
2 11
2 12

3 and 4 should not be returned. I figured if I added the code that is commented out in the CTE that should give me everything but I think I get caught in an infinite loop.

View 8 Replies View Related

SQL Server 2012 :: Can Use Lag To Avoid Recursive CTEs?

May 13, 2014

Let's say I have a scalar functions that I'd like it's input to be the output from the previous row, with a recursive CTE I can do the following:

;with rCTE(iterations,computed) as (
select 0 [iterations], 1e+0 [computed]
union all
select iterations+1,dbo.f(computed)
from where rCTE
where iterations < 30
)
select * from rCTE

Thus for each iteration, is the nTh fold of the function f applied to 1. [e.g 5 is f(f(f(f(f(1)))))]

However, for some illogical reason this relatively simple function did lots of read and write in tempdb. Can I reform this query somehow to just use lag instead? I know for a fact I only want to get let's say 30 iterations. It'd be very nice to be able to enjoy a window spool which will spawn a worktable with minimal IO.

I know I can put 30 rows into a table variable and do a quirky update across it, but Is there a nice way to do this without doing some sort of hack.

View 4 Replies View Related

SQL Server 2012 :: Recursive Concatenation Of Parent Elements

Jul 28, 2015

I have a hierarchical structure for mapping products to categories, categories go 3 levels deep (depth is defined in articlegroups.catlevel, 0 being the main category and traversing down to lower category level 2). Also, a product may be in more than 1 category(!).

product details are stored in `[products]`
articlegroups are defined in `[articlegroups]`
and the mapping of the products to the articlegroups are defined in `[products_category_mapping]`

Now, I want to retrieve index the full category path for each item, so with the data provided below, I'd expect these 2 rows as a result:

id categorystring
2481446 Taarttoppers > Taarttoppers grap'pig
2481446 Bruidstaart > Taarttoppers > Grappig

Now I can get the separate fields via a statement like this:

SELECT ga.slug_nl as slug_nl_0
FROM articlegroups ga
INNER JOIN products_category_mapping pcm ON pcm.articlegroup_id=ga.id
INNER JOIN products gp on gp.id=pcm.artikelid
WHERE gp.id=2481446

[code]....

View 9 Replies View Related

SQL Server 2012 :: Add Column Names As Total And Subtotal For NULL Values?

Jan 21, 2014

How do I add column names as Total and SubTotal for NULL values.

SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)

[code]....

View 9 Replies View Related

SQL Server 2014 :: Recursive Query Using CTE

Sep 14, 2015

sql recursive query, for example purpose i m providing sample table with insert script

CREATE TABLE Details(
parentid varchar(10), DetailComponent varchar(10) , DetailLevel int)
GO

INSERT INTO Details
SELECT '','7419-01',0 union all
SELECT '7419-01','44342-00',1 union all
SELECT '7419-01','45342-00',1 union all
SELECT '7419-01','46342-00',1 union all
SELECT '7419-01','47342-00',1 union all
SELECT '7419-01','48342-00',1 union all
SELECT '7419-01','49342-00',1 union all

[code]....

From the above table data i want a search query , for example if I search data with "52342-00" I want output to be below format using CTE.

NULL,'7419-01',0
'7419-01','52342-00',1
'7419-01','52342-00',1
'52342-00','54342-00',2
'54342-00','54552-00',3
'54552-00','R111-54',4
'R111-54','R222-54',5
'R222-54','52342-00',6

View 6 Replies View Related

Show Child Subtotal In Parent's Subtotal Row

Apr 1, 2008



My report has two groups, company and error type for each company.
Company1

Functional Error
Data Error
Other Error
My goal is to show the subtotals for each error types when I show Company's subtotal/total; in the group 1 footer area.
Total for Functional Error:
Total for Data Error:
Total for Company1:

Please help me in figuring out how to do that.

Thank you in advance for your help.

View 3 Replies View Related

Subtotal In Query

Jul 20, 2005

Hi,WITH SQL SERVER QUETY, I have output likePRJ item qty=================P1 I1 10P1 I1 20P1 I2 10P2 I2 10P2 I3 10================I WANT TO MANIPULATE IT LIKE THISPRJ item qty=================P1 I1 30P1 I2 10---------------P1 * 40---------------P2 I2 10P2 I3 10---------------P2 * 20---------------===============PRJ * 60===============SHOW DATA AND SUBTOTAL AND GRAND TOTAL IN ONE RESULTSETCAN ANYBODY HELPTHANKS IN ADV.T.S.NEGI

View 2 Replies View Related

How To Convert Recursive Function Into Recursive Stored Procedure

Jul 23, 2005

I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod

View 4 Replies View Related

How To Query The Using Recursive?

Jun 10, 2007

 
Hello,
I have the following tables:

Article(articleID,CategoryID,ArticleTitle)
Categories(categoryID,ParentID,CategoryTitle)
I am trying to retrieve the main category ID for a specific article ID.
For example lets say I have this data:
Article: 

1, 10 , "some title"
2,10,"some title"
3,11,"some title"
Categories:
1, NULL , "some title"
2, 1, "some title"
10, 2, "some title"
11, 10 , "some title"
 
In this example I want to know who is the main category of article 3.
The query should return the answer: 1
Thats because:

The article ID 3 is inside category 11.
Parent for category 11 is 10.
Parent for category 10 is 2.  
Parent for category 2 is 1
and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category.
Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables)
Do you have any suggestions for such query?
Thanks all.
 

View 1 Replies View Related

Recursive Query ..

Jan 18, 2008

Recursive quey to show products with "custom defines fields" related by Classifications, instead of per product Hello, I’m working on a project ..  .



I’m desperating due to the
complex (for me and also I think for some others) sql query that I need to
write, to show the products with his “custom defined fields� that are inside a ProductsFieldsByClassification
table that holds this mentioned “custom defined fieds� according to the Classifications
table, on where the Products can be found trought the productsClassifications
table.


CustomFields can be defined
and set for the products, trought his Classifications (instead of define a
custom field for each product (that consume a lot of data), I decide to use it as
I explain)

 

I will to know the properly
SQL QUERY to show a list of products with the ProductsFieldsByClassifications and ProductsFieldsValuesByClassifications:

 


As example on
a Requested ID_Classification = 16 (Torents/Games/Dreamcast/PAL), the products must be show with the
ProductsFields and Values that has the DBA for the:

·        
requested
ID_Classification

o       
PAL (ID_Classification: 16)

·        
AND all
the Classifications that belongs above (trought ID_ParentClassification) that are :

o       
Torrents (ID_Classification:
1) that will show the products values for the “Size�

o       
Games (ID_Class..:4) ß this classification has no CustomFields so none from this one.

o       
Dreamcast (ID_Class..:14 )
that will show his ID_Classification(14) product field “Levels� value (but not “AllowSave� as not have value for any product)

 




Hmnn i show a graphic that i design for (feel to click over to see at correct resolution) 

 
 
 

I also write asp.net tutorials. For those interested see my blog at http://abmartin.wordpress.com 
 

 

View 2 Replies View Related

Recursive SQL Query

Sep 21, 2004

I have a tough issue with a query.

I have the following structure


Table: Users

UserId ParentUserId
1 1
2 1
3 2
4 1
5 4
6 5


I need to write a stored procedure that takes in UserId as parameter and returns everyone under him/her.

So if Param=1 it would return result set of 2,3,4,5,6

Anyone done this before or have any ideas?

Thanks,
ScAndal

View 1 Replies View Related

Recursive Query Help

Jul 20, 2005

Hi,

Does anyone know how to do an sql recursion queries?

I believe it involves a view with a union.

I have a User Table and in that table i have a employee_id and a
boss_id. What i'd like to do is to find all employees under a certain
boss.
For example,

Employee_ID Boss_ID
1
2                     1
3                    
4                     3
5                     2

So if i'd like to know who are under the employee_id = 1 it will return
employee_id 2 and 5 since employee 2 also is the boss of employee_id =
5.

To do that i'd have to have recursion query.

Thanks,

View 2 Replies View Related

Recursive Sql Query

Sep 29, 2004

Hi,

i have the following table structure:

id | parentID
1 | NULL
2 | 1
3 | 2
4 | 3

im having trouble in creating a recursive query to return all the parents of a particular id. Have anyone ever done this?

thanks in advance,

View 7 Replies View Related

Can A Recursive Query Do This?

Dec 23, 2004

I am wondering if there is some type of recursive query to return the values I want from the following database.

Here is the setup:

The client builds reptile cages.

Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.

The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.

PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.

Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb

Here is a quick schema:

Table: PRODUCT
--------------------------
PRODUCTIDPK
PRODUCTNAMEnVarChar(30)


Table: ASSEMBLY
--------------------------
PRODUCTIDPK(FK to PRODUCT.PRODUCTID)
COMPONENTIDPK(FK to PRODUCT.PRODUCTID)
QTYINT


I can write a query that takes the PRODUCTID, and returns all



PRODUCT
=======
PRODUCTIDPRODUCTNAME
--------------------
1Cage Assembly - Solid Sides
2Cage Assembly - Split Back
3Cage Assembly - Split Sides
4Cage Assembly - Split Top/Bottom
5Cage Assembly - Split Back and Sides
6Cage Assembly - Split Back and Top/Bottom
7Cage Assembly - Split Back and Sides and Top/Bottom
833S - Aluminum Divider
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
12Connector Socket
13Connector Screws



ASSEMBLY
=========
PRODUCTIDCOMPONENTQTY
---------------------
198
1104
1111
211
281
311
381
411
481
511
582
611
682
711
783
11128
11138



I need a query that will give me all parts for each PRODUCT.

Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"

The results would be:


PRODUCTIDPRODUCTNAME
--------------------
2Cage Assembly - Split Back
1Cage Assemble - Solid Back
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
833S - Aluminum Divider
12Connector Socket
13Connector Screws

Is it possible to write such a query or stored procedure?

View 4 Replies View Related

Recursive Query

Dec 6, 2007

Can you please help me to write a recursive query in sql server 2000?

I have got a table WORKORDER. wonum and parent are the two columns in the table. A wonum can have children.Those children can have children, so on. if i am giving a wonum,it should display all the children ,their children and so on.

Sample data in the table is as follows

wonum parent
7792 NULL
7793 7792
165305 7793
7794 7792
7795 7792

eg:
7792 is a workorder,which has got children and grand children

7793 is a child of 7792
165305 is a child of 7793
7794 is a child of 7792 and
7795 is a child of 7792

When I give the 7792 in the query,it should fetch all the children and grand children,etc.
output should be :
7793
165305
7794
7795

How can we do that?

View 16 Replies View Related

Recursive Query ??

Jul 23, 2005

Went looking for an answer but not really sure what phrases to lookfor. Just decided to post my question.I have a collection of groups which contain items. I also have acollection of users which can be assigned permissions to both groupsand individual items. If a user has permission to a group then the userhas that permission to each of the items in the group. I need a querywhich will return all the items and permission for a particular user.Here is the code for creating the tables and populating them.CREATE TABLE [Account] ([Name] VARCHAR(10))INSERT INTO [Account] VALUES ('210')INSERT INTO [Account] VALUES ('928')INSERT INTO [Account] VALUES ('ABC')CREATE TABLE [AccountGroup] ([Name] VARCHAR(10))INSERT INTO [AccountGroup] VALUES ('Group1')INSERT INTO [AccountGroup] VALUES ('Group2')CREATE TABLE [AccountGroupMembership] ([GroupName] VARCHAR(10), [AccountName] VARCHAR(10))INSERT INTO [AccountGroupMembership] VALUES ('Group1', '210')INSERT INTO [AccountGroupMembership] VALUES ('Group1', 'ABC')INSERT INTO [AccountGroupMembership] VALUES ('Group2', '928')INSERT INTO [AccountGroupMembership] VALUES ('Group2', 'ABC')CREATE TABLE [Permission] ([User] VARCHAR(10), [Item] VARCHAR(10), [ItemType] VARCHAR(1)-- 'A' for account, 'G' for account group, [ReadPerm] INT, [WritePerm] INT)INSERT INTO [Permission] VALUES ('john', '210', 'A', 1, 0)-- readaccess to 210 accountINSERT INTO [Permission] VALUES ('john', 'Group1', 'G', 1, 1)--read/write access to Group1 groupINSERT INTO [Permission] VALUES ('mary', '928', 'A', 0, 1)-- writeaccess to 928 accountThe simple querySELECT * FROM [Permission] WHERE [User] = 'john'returnsUser Item ItemType ReadPerm WritePerm---------- ---------- -------- ----------- -----------john 210 A 1 0john Group1 G 1 1but what I really want is (notice that Group1 has been replaced withthe two members of Group1)User Item ReadPerm WritePerm---------- ---------- ----------- -----------john 210 1 0john 210 1 1john ABC 1 1(Forget for the moment that 210 is listed twice with differentpermissions. I could take the result and do some sort of union to least(or most) restrictive permissions.)

View 4 Replies View Related

Recursive Query

Jul 20, 2005

Hi there,Need a little help with a certain query that's causing a lot of acidin my stomach...Have a table that stores sales measures for a given client. The salesmeasures are stored per year and there could be multiple salesmeasures every year per client. There is another field called lastupdate date. If there are multiple sales measures then need to selectthe one that's been entered last based on this field. Also, if thereis no sales measure data for current year then I need to return thelast year's data for which sales measure has been entered. Forexample: if client #1 has sales measure value of $200 for 1999 andnothing since, then I need to return $200 for any year following 1999.So the query would look something like this:SELECT client_name, sm_dollars FROM <tables>Based on the DDL at the bottom I would expect to get back: c1, 100;c2, 200The way I am doing it now is with correlated subqueries (3 to beexact) that each do an aggregate and join back to the original table.It works, but it is notoriously slow. SQL Server is scanning theindex and does a merge join which in a large query takes %95 of thetime. Here is the part of the query plan for it:| | | | | | |--MergeJoin(Inner Join, MANY-TO-MANYMERGE:([sales_measure].[client_id])=([sales_measure].[client_id]),RESIDUAL:(([sales_measure].[client_id]=[sales_measure].[client_id]AND [sales_measure].[tax_year]=[sales_measure].[tax_year]) AND[Expr1013]=[sales_measure].[last_update_date]))| | | | | | |--StreamAggregate(GROUP BY:([sales_measure].[client_id],[sales_measure].[tax_year])DEFINE:([Expr1013]=MAX([sales_measure].[last_update_date])))| | | | | | | |--MergeJoin(Inner Join, MERGE:([sales_measure].[client_id],[Expr1010])=([sales_measure].[client_id], [sales_measure].[tax_year]),RESIDUAL:([sales_measure].[client_id]=[sales_measure].[client_id] AND[sales_measure].[tax_year]=[Expr1010]))| | | | | | ||--Stream Aggregate(GROUP BY:([sales_measure].[client_id])DEFINE:([Expr1010]=MAX([sales_measure].[tax_year])))| | | | | | | ||--Index Scan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)| | | | | | ||--Index Scan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)| | | | | | |--IndexScan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)There are two indexes on sales measure table:sales_measure_pk - sales_measure_id (primary key) clusteredsales_measure_idx1 - client_id, tax_year, last_update_date, sm_dollarssales_measure table has 800,000 rows in it.Here is the rest of the DDL:IF OBJECT_ID('dbo.client') IS NOT NULLDROP TABLE dbo.clientGOcreate table dbo.client (client_idintidentityprimary key,client_namevarchar(100)NOT NULL)GOIF OBJECT_ID('dbo.sales_measure') IS NOT NULLDROP TABLE dbo.sales_measureGOcreate table dbo.sales_measure(sales_measure_idintidentityprimary key,client_idintNOT NULL,tax_yearsmallintNOT NULL,sm_dollarsmoneyNOT NULL,last_update_datedatetimeNOT NULL)GOCREATE INDEX sales_measure_idx1 ON sales_measure (client_id, tax_year,last_update_date, sm_dollars)GOINSERT dbo.client(client_name)SELECT'c1' UNION SELECT 'c2' UNION SELECT 'c3'GOINSERTdbo.sales_measure(client_id, tax_year, sm_dollars,last_update_date)SELECT1, 2004, 100, '1/4/2004'UNIONSELECT2, 2003, 100, '1/3/2004'UNIONSELECT 2, 2004, 150, '1/4/2004'UNIONSELECT2, 2004, 200, '1/5/2004'The view that I use to calculate sales measures:CREATE VIEW sales_measure_vw ASSELECTsm.*FROM sales_measure smINNER JOIN (SELECT sm2.client_id, sm2.tax_year,MAX(sm2.last_update_date) as last_update_dateFROM sales_measure sm2INNER JOIN (SELECT sm4.client_id, MAX(sm4.tax_year)as tax_yearFROM sales_measure sm4 GROUP BYsm4.client_id) sm3on sm3.client_id = sm2.client_idand sm3.tax_year = sm2.tax_yearGROUP BY sm2.client_id, sm2.tax_year ) sm1ON sm.client_id = sm1.client_id ANDsm.tax_year = sm1.tax_year ANDsm.last_update_date = sm1.last_update_dateAny advice on how to tame this would be appreciated. Also, any adviceon the indexes would help as well.ThanksBob

View 14 Replies View Related

Recursive Query

Dec 11, 2007

I'm trying to get from the first table bellow to the second one using recursive queries. Could you help me on this issue? I want to be able to group by project and employee id and then if the dates overlap then to get the min and max dates in one row

ProjectID EmpId StartDate EndDate
P01 E01 2007-10-01 2007-10-19
P01 E01 2007-11-01 2007-11-20
P01 E01 2007-11-15 2007-11-25
P01 E02 2007-11-30 2007-12-31


ProjectID EmpId StartDate EndDate
P01 E01 2007-10-01 2007-10-19
P01 E01 2007-11-01 2007-11-25
P01 E02 2007-11-30 2007-12-31

Thank you in advance

View 1 Replies View Related

Help With Recursive Query

Nov 7, 2007

I've been struggling with this for while. It doesn't seem that hard, but I can't seem get the results right. I've tried several SELECT queries and also played with CTE but I'm quite a novice in that dept. I have searched this forum and read many similar posts but I can't seem to get it quite right.

Suppose I have a table (Categories) with parent categories and sub categories with the following example data:

Id ParentId Category
1 null Items
2 null Autos
3 1 Books
4 2 Honda
5 1 DVDs
6 1 Furniture
7 2 BMW

Can someone suggest the most efficient way to get an appropriate result set that I could use to display each Parent Category followed by each appropriate Sub Category on a web page?

For example:

Autos
Honda
BMW

Items
Books
DVDs
Furniture

I'd appreciate any advice and thanks in advance.

View 5 Replies View Related

Recursive Query?

Jul 23, 2006

I have a set of data and I need to actually add a column to the result set based on information contained within the query. For example,

SalesmanID  SalesBudget   SalesRegion      Product      Amount
1                      1000                 USA                   Soap          100.50
1                      1000                 USA                   Milk               50.50
5                       2000                OZ                      Bread             2.50
1                       1000                USA                   Bread             2.50
3                        350                 OZ                      Nappies         4.50

Notice that Salesbudget is related to the salesmanid and doesn't change. I need to be able to add a column to this query that sum the total budget for the saleregion so output looks like this

SalesmanID  SalesBudget   SalesRegion      Product      Amount   RegionBudget
1                      1000                 USA                   Soap          100.50    3500
1                      1000                 USA                   Milk               50.50    3500
5                       2000                OZ                      Bread             2.50    2350
2                       1500                USA                   Bread             2.50    3500
3                        350                 OZ                      Nappies         4.50    2350

So I need to do a sum distinct on salesbudget by region and append to my query as a column, is this possible? If so how?

It is important that I do it this way as I will have parameters in my query that might restrict which lines are returned. For instance show all bread sales and that way I only what to sum region budgets for these.

I could do it with 2 queries but needs to be 1 as I am using a matrix report that does'nt contain a sum distinct function and is contrained by scope.

Thanks in advance

Damien

View 9 Replies View Related

Help With A Recursive Query!!!

Dec 20, 2007

i have two tables, one called P_TAXONOMY with these columns:

IDObject TaxID
DB1 259

and one hierarchical table called (TAXONOMY) with this columns:

TaxID TaxIDParent tax_name taxlevel
259 256 P.O 3

256 4 A 2

4 1 B 1

1 0 C 0


i get the TaxID from P_TAXONOMY and i have to look for recursively its father (level 0) in the TAXONOMY table; I'm pretty sure the solution to this involves some type of recursive query but if anyone here can help me out I'd really appreciate it.

i have the next query but only for 3 levels, i need it for n levels (from any leaf)

select stf.tax_name
from TAXONOMY stf,
(select st0.TaxIDParent
from TAXONOMY st0, (select st.TaxIDParent
from P_TAXONOMY spt, TAXONOMY st
where spt.TaxID = st.TaxID ) as u
where st0.TaxID = u.TaxIDParent ) as cons
where stf.TaxID = cons.TaxIDParent
group by tax_name


Thanx
Diego Bayona.


pd:
for example:
the root of TaxID = 259 (P_TAXONOMY) is 1 (TAXONOMY)

View 1 Replies View Related

Recursive Query --- Very Urgent

May 15, 2007

Hello. I have a question. I have a table that keeps track of
claims being made by clients (like insurance claims). There is a table which
has 2 fields. Field1 and Field2. Field1 has the original claim number. If an
adjustment is made to the claim, a new claim number will be generated. For example
if a person pays $100 and files a claim, he is issued a claim number which goes
into Field1. If that person spends some more money towards the same issue and
then files another claim then he is given a new claim number which is stored in
Field2.

Field1--------------Field2
--------------------------------
100--------------------150
150--------------------200
200--------------------250
250--------------------Null (No more adjustments made)
and so on.

So in the query I will supply a claim number (say 100). I
need to get the history of all the claims attached to it. In this example 100,
150, 200, 250.

Also if the claim number provided is 200 I need to get the
original claim number (which is 100) meaning I need to back track and forward
track too. I need this in SQL Server 2000.

I hope I am clear enough. Thanks.

View 3 Replies View Related

Recursive Query Problem

Jul 18, 2006

I have a simple 1:M table called UserFriends

UserID, FriendID
1 2
3 9
4 8
2 4
2 6
2 3
5 7
2 1

I would like to write a query that returns a user's friends and all their friends. For example, getUserFriends(1) would return 2, 4, 6, 3, 8, 9

I wrote a recursive function but I'm having a little trouble with it. Basically, the problem is that once a user has been processed that user should be excluded.

Here is my function:


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER FUNCTION dbo.GetUserConnections (@UserID INT)
RETURNS @Connections TABLE (FriendID INT, UserID INT, ProcessedFriend INT) AS
BEGIN
DECLARE oCursor CURSOR LOCAL FOR

SELECT FriendID, UserID FROM UserFriends WHERE
UserID=@UserID AND FriendID NOT IN (
SELECT ProcessedFriend FROM @Connections WHERE UserID=@UserID
) AND (
NOT EXISTS(
SELECT UserID FROM @Connections WHERE UserID=@UserID)
)
OPEN oCursor

DECLARE @LocalUserID int, @LocalFriendID INT

FETCH NEXT FROM oCursor INTO @LocalFriendID,@LocalUserID

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF NOT Exists (SELECT FriendID FROM @Connections WHERE FriendID=@LocalUserID)
BEGIN
INSERT @Connections (FriendID, UserID, ProcessedFriend) SELECT @LocalFriendID, @UserID,@LocalFriendID
INSERT @Connections (FriendID, UserID, ProcessedFriend)
SELECT FriendID, @UserID, @LocalFriendID FROM dbo.GetUserConnections(@LocalFriendID)
WHERE (FriendID NOT IN (SELECT FriendID FROM @Connections))
END
FETCH NEXT FROM oCursor INTO @LocalFriendID,@LocalUserID
END

CLOSE oCursor
DEALLOCATE oCursor
RETURN
END






GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View 1 Replies View Related

How To Write A Recursive Query?

Dec 23, 2004

I am wondering if there is some type of recursive query to return the values I want from the following database.

Here is the setup:

The client builds reptile cages.

Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.

The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.

PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.

Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb

Here is a quick schema:

Table: PRODUCT
--------------------------
PRODUCTIDPK
PRODUCTNAMEnVarChar(30)


Table: ASSEMBLY
--------------------------
PRODUCTIDPK(FK to PRODUCT.PRODUCTID)
COMPONENTIDPK(FK to PRODUCT.PRODUCTID)
QTYINT


I can write a query that takes the PRODUCTID, and returns all



PRODUCT
=======
PRODUCTIDPRODUCTNAME
--------------------
1Cage Assembly - Solid Sides
2Cage Assembly - Split Back
3Cage Assembly - Split Sides
4Cage Assembly - Split Top/Bottom
5Cage Assembly - Split Back and Sides
6Cage Assembly - Split Back and Top/Bottom
7Cage Assembly - Split Back and Sides and Top/Bottom
833S - Aluminum Divider
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
12Connector Socket
13Connector Screws



ASSEMBLY
=========
PRODUCTIDCOMPONENTQTY
---------------------
198
1104
1111
211
281
311
381
411
481
511
582
611
682
711
783
11128
11138



I need a query that will give me all parts for each PRODUCT.

Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"

The results would be:


PRODUCTIDPRODUCTNAME
--------------------
2Cage Assembly - Split Back
1Cage Assemble - Solid Back
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
833S - Aluminum Divider
12Connector Socket
13Connector Screws

Is it possible to write such a query or stored procedure?

View 1 Replies View Related

Complex Recursive Query

Jan 17, 2005

I have a complex query that I am strugling with in MS SQL Server 2000. I am trying to get all of the websites a user has control of. My schema is as follows:

A user can control many companies, and a company can be controlled by many users. A company can also control other companies (thus a recursive parent relationship). A company can have many websites, but a website can only belong to one company. Thus the design:


USER TABLE
User_ID
User_Name

COMPANY TABLE
Company_ID
Company_Name
Company_ID_Parent (recursive)

USER_COMPANY TABLE
User_ID
Company_ID

WEBSITE TABLE
Website_ID
Company_ID (Foreign Key)


That said, how do I get a list of websites that a user is associated with... meaning, all of the websites that belong to a company that either the user controls directly, or a child-company of a company that either the user controls directly (at any depth).

Getting the websites is actually easy. I need the recursive part figured out.

Thx in Advance.

View 13 Replies View Related

Recursive Relationship Query

May 21, 2006

Hi all,

Im really confused and need some help please from some of you clever people.

I know the basics of SQL but Ive no idea how to write a query to do this.

Basically, we have two tables, the first containing a list of people and the second containing two relationships to the person table. The first is the parent relationship and the second the child relationship. Each person may be related to one or more other people (e.g. father, son, brother, sister etc.). If you can imagine we have a few hundred people in the person going back a couple of hundred years.

Now Ive set the scene Im really trying to create a query that can navigate all the relationships from a set point (Person).

Ultimately I would like to (given a PersonID, navigate the full PERSON_ASSOCIATION table to find:

a. All people below the PersonID (if there are any)
b. All people above the PersonID (if there are any)

For example, in the data below there is somebody called John Smith.

Going down the PERSON_ASSOCIATION table I would like to show:

John Smith has a brother called Andrew Jones who has a brother-in-law called Stephen Barnes

John Smith has a wife called Jane Wallace

John Smith has a daughter called Mary Joseph

What I am going round in circles on is the multiple relatationships, e.g. A is related to B who in turn is related to D who in turn is related to E therefore A is related to E.




PERSON
ID NAME
1 John Smith
2 Andrew Jones
3 Jane Wallace
4 Steven Barnes
5 Mary Joseph
98 Me
99 Joanne




PERSON_ASSOCIATION
ID PARENT_PERSON_ID CHILD_PERSON_ID RELATIONSHIP
1 1 2 Brother
2 1 3 Wife
3 1 5 Daughter
4 2 4 Brother-In-Law
5 98 1 Father
6 98 99 Husband


Can somebody please point me in the right direction by explaining how I can write some SQL that will navigate (and iterate through) all the PERSON and PERSON_ASSOCIATION tables.

Im sure there must be a straight forward way of doing this.

One final thing, if it helps, Im hoping to eventually end up with a stored procedure which I can basically pass in a PERSON_ID and it returns a list of all the relationships from that person.

MANY BIG thanks in advance

Im really sorry but it is driving me crazy

Mike

View 1 Replies View Related

Recursive Query Problem

Aug 30, 2007

So I have tblJobItem with fields

JobItemName, JobItemSublevel, JobItemParent, JobItemChild1, JobItemChild2

JobItemName is the name of item name
sublevel goes from 0 - 3

if sublevel is 0 then parent, child1, child2 are null
if sublevel is 1 then child1, child2 are null and parent is value of sublevel 0 jobitemname

if sublevel is 2 then child2 is null and parent is value of sublevel 0 jobitemname and child1 is name of sublevel 1 jobitemname

all that said Im trying to write a recursive query to get he heirachy structure -- the following gives me an error that there is no anchor query for tbljobitem

WITH tblJobItem (JobItemName, JobItemSubLevel, JobItemParentName, JobItemChildName) AS
(
SELECT a.JobItemName, a.JobItemSubLevel, a.JobItemParentName, a.JobItemChildName FROM tblJobItem AS a
UNION ALL

SELECT a.JobItemName, b.JobItemSubLevel, a.JobItemParentName, a.JobItemChildName
FROM tblJobItem AS a

INNER JOIN tblJobItem AS b ON a.jobitemsublevel = b.JobItemSubLevel
)

SELECT *
FROM tbljobitem
ORDER BY JobItemIDID

how do i make this work with the table structure provided.

View 15 Replies View Related

Help Sorting A Recursive Query

Jul 27, 2007

Guys, I need help sorting a recursive query. This is my table

CTLG Table
txtID(PK)
txtParentID(FK)
numSortID
txtKeywords
txtTitle
memContent

I'm using txtParentID as the FK, which matches with txtID, to create the recursion.


This is my current Query

This is my current Query:




Code Snippet
WITH tree (data, id, level, pathstr, numSortID, memContent)
AS (SELECT txtTitle, txtid, 0,
CAST('' AS NVARCHAR(MAX)), numSortID, memContent
FROM CTLG
WHERE txtParentID IS NULL
UNION ALL
SELECT txtTitle, V.txtid, t.level + 1, t.pathstr + '>' + V.txtTitle, V.numSortID, v.memContent
FROM CTLG V
INNER JOIN tree t
ON t.id = V.txtParentID)
SELECT SPACE(level) + data as data, id, level, pathstr, numSortID, memContent
FROM tree



The output is this:








Data
ID
Level
pathstr
NumSortID

Undergraduate Catalog
1
0

1

History
12
1
>History
7

Academic Calendar
14
1
>Academic Calendar
8

Preface
2
1
>Preface
2

NonDiscrimination Statement
3
1
>NonDiscrimination Statement
3

Accreditation
4
1
>Acreditation
4

Memberships
5
1
>Memberships
5

Mission Statement
8
1
>Mission Statement
6

Fall Calendar
20
2
>Academic Calendar>Fall Calendar
1

Winter Calendar
21
2
>Academic Calendar>Winter Calendar
2

Summer Calendar
22
2
>Academic Calendar>Summer Calendar


3



I need my out put to look like the following:

Undergraduate Catalog Level (0) NumSortID (1)
Preface (1) (2)
NonDiscrimination Statement (1) (3)
Accreditation (1) (4)
memberships (1) (5)
Mission Statement (1) (6)
History (1) (7)
Academic Calendar (1) (8)
Fall Calendar (2) (1)
Summer Calendar(2) (2)
Winter Calendar (2) (3)

The Order that I would like to have is based on NumSortID and by Levels. Like the above example.

View 7 Replies View Related

Recursive Query With Different Tables

Feb 29, 2008



Want to populate a navigation tree and looking for advise on how to implement the queries with (if possible) only one trip to the database.

To simplify I will give an example using made-up tables to populate MY-ARMY treeview:
Assume Table People:

PeopleID
ParentPeopleID
Name
Rank

Table General

PeopleID
<General's attributes>

Table Commander

PeopleID
<Commander's attributes>

Table Soldier

PeopleID
<Soldier's attributes>



If I build a recursive query to see MY-ARMY, is there a way to get each unique table's attribute?
I understand that the result is not uniform, therefore needs to be broken in different JOIN SELECTs, but then... how to avoid calling the recursive query multiple times?

Thx,
Uri

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved