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


ADVERTISEMENT

Transact SQL :: Recursive CTE Parent Child Query

Oct 31, 2015

I have a Recursive CTE for TFS database which gives me below results:

Parent (User Story) level 0
             -------------.Task(Dev) and ask(QA) --

level  1
    BUG level 2
                ---------------Task (Dev) and Task(QA)
level2 

So, My ParentID column keeps two ParentId one for User story which keeps the child tasks and child bugs and another for Bug Child tasks

I need to update this results and want to see all of the tasks under User story so the result I want is:

User Story:

 Task(dev and QA)
 Task (Dev and QA ) but its should know that these tasks are the bug tasks.

View 10 Replies View Related

Transact SQL :: Recursive Query To Find Child Of A Parent Until Last Leaf

Oct 22, 2015

I need to write recursive query to find child of a parent until the last leaf. Below is my code. 

;WITH Parent AS(
SELECT [ParentID],Value
FROM[DynamicColsValues_TP1]
WHEREValue IS null
UNION ALL
SELECT t1.[ParentID],T1.Value, FROM DynamicColsValues_TP1 t1 INNER JOIN Parent t2
ON t1.[ParentID]=t2.[ParentID]
)
SELECT * FROM Parent option (maxrecursion 0)

When I execute this code. It is returning me millions of rows. Whereas  i have only 20 rows in a table max 40 rows it should return.

View 7 Replies View Related

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

SQL Server 2012 :: Query To Select Parent Details From Child Table

Mar 3, 2015

I have a scenario,

We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.

Equipment table looks like :

Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177

There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.

Select query where i need to select the Code for all Parent_Id's.

View 8 Replies View Related

SQL Server 2012 :: Query To Generate Relationship (Parent Child Hierarchy From A Table)

Jul 18, 2015

I am working on a query to generate parent child hierarchy from a table.

Table has below records.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable

[Code] ...

how to achieve this.l tried with temp tables it doesn't work.

View 5 Replies View Related

SQL Server 2012 :: Finding Lowest Level Descendants In Hierarchy

Mar 11, 2015

I've got a fairly large hierarchy table and I'm trying to put together a query to find the lowest level descendants of the hierarchy. I think there must be some way to use the "Breadth-first" approach that's stated in the MSDN technet sites about SQL Server HierarchyID but i'm not sure how to write the necessary T-SQL to traverse that. I know I can get all the descendants of a parent node like this

SELECT *
FROM AdventureWorks2012.HumanResources.Employee
WHERE OrganizationNode.IsDescendantOf(@ParentNode) = 1

However, this query returns all levels for that parent's branch. If I just wanted list of employees that were at the lowest level of the branch(es) for this parent node, how would I do this?

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 2012 :: Group By Parent With One Child And Multiple Child Information?

Jul 25, 2014

Basically i have three Tables

Request ID Parent ID Account Name Addresss
1452 1254789 Wendy's Atlanta Georgia
1453 1254789 Wendy's Norcross Georgia
1456 1254789 Waffle House Atlanta Georgia

Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789

Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789

I want the Result

Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2

View 1 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 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

SQL Server 2012 :: Create XML With Parent And Child Element

Jul 8, 2015

I want to create an XML file with the below format.

office_tel and mobile are fields of a table.

<fields>
<office_tel>
<value>1234</value>
</office_tel>
<mobile>
<value>99999</value>
</mobile>
</fields>

View 2 Replies View Related

SQL Server 2012 :: Purging Data From Master And 5 Child Tables

Jul 5, 2014

I have 6 tables which are very huge in row count and records needs to deleted which are older than 8 days.

Little info: Every day, 300 Million records are inserted in below 7 tables. we should maintain only 8 days worth of data in below tables. How to implement Purge script which can delete records in all tables in the same time and with optimized parallelism.

Master table which has [ID],[Timestamp]
Table Name: Sample - 2,578,106

Child tables: Foreign key [ID] is common for all the tables. There is no timestamp column in child table. So the records needs to deleted based on Min(ID) from Sample

dbo.ConnectionDB - 1,147,578,048
dbo.ConnectionSS - 876,458,321
dbo.ConnectionRT - 118,133,857
dbo.ConnectionSample - 100,038,535
dbo.Command - 100,032,235

View 9 Replies View Related

SQL Server 2012 :: How To Get Most Recent And Oldest From Joins To A Child Table

Jul 23, 2014

I have the following tables in my DB

Employee table - This table has EmployeeID, Name, DOB.

EmployeeDesignation table - 1 Employee can have many designations with each having an effective date. There is no flag to indicate which among multiples is the current entry. You can only figure it out by the newest/oldest EffectiveDate. I want to get the most recent and the oldest for each employee.

EmployeeSalaryHistory table - Structure/Design is similar to EmployeeDesignation table. I want to get the starting salary and current salary for each employee.

I want my query to output me the following fields...

EmployeeID
EmployeeName
EmployeeDOB
EmployeeStartingDesignation
EmployeeCurrentDesignation
EmployeeStartingSalary
EmployeeCurrentSalary

Here is a piece of code to generate sample data sets

DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATE)
INSERT @Employee VALUES (101, 'James Bond', '07/07/1945'), (102, 'Tanned Tarzan', '12/13/1955'), (103, 'Dracula Transylvanian', '10/22/1967')

DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATE)
INSERT @EmployeeDesignation VALUES (101, 'Bond Intern', '01/01/1970'), (101, 'Bond Trainee', '01/01/1975'), (101, 'Bond...James Bond', '01/01/1985')

[Code] ....

Currently, I have a query to get this done which looks as below. Since I have more than 8K employees with each having multiple Designation and Salary entries, my query is taking forever.

selecte.EmployeeID, e.EmployeeName, e.EmployeeDOB,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingDesignation,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentDesignation,

[Code] ....

View 5 Replies View Related

SQL Server 2012 :: Delete Unmatching Records In Child Table

Feb 24, 2015

I've 2 tables ResumeSkill (Child table) and Skill (Parent table), There are duplicates in the parent table and after removing the foreign key constraint in child table deleted all duplicate values from Parent table. But those deleted duplicate values has references in child table which need to be deleted now.

ResumeSkill Skill

Id SkillId
SkillId Name

I want to delete all the records from ResumeSkill that dont have matching skillId in Skill table.

View 2 Replies View Related

SQL Server 2012 :: Statement To Group Rows As Multiple Child Under Single Parent?

Sep 18, 2014

I've 2 tables QuestionAnswers and ConditionalQuestions and fetching data from them using CTE join and I'm seeing repetitive rows (not duplicate) like, If you have multiple answers for 1 question, the output is like

where london
where paris
where toronto

why us
why japan
why indonesia

I want to eliminate the repetitive question and group them as parent child items.

with cte as (
select cq.ConditionalQuestionID from ConditionalQuestions cq
inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=5 and qa.IsConditional='Y')
select distinct q.Question, a.Answer from QuestionAnswers qa
inner join Answers a on a.AnswerID = qa.AnswerID
inner join Questions q on q.QuestionID = qa.QuestionID
inner join cte c on c.ConditionalQuestionID = qa.QuestionID;

View 4 Replies View Related

GROUP BY Speed Up Query On Data That Is Already At Lowest Granularity?

Jul 9, 2015

I have just been running a query which I was planning on improving by removing a redundant GROUP BY (there are about 20 columns, and one of the columns returned is atomic, so will mean that the "group by" will never manage to group any of the data) but when I modified the query to remove the grouping, this actually seems to slow the query, and I can't see why this would be the case.

Both queries return the same number of rows (69000), as I expected, and looking at the query plan, then they look nearly identical, other than at the start, there is a "stream aggregate" and "sort" being performed. The estimated data size is 64MB for the non-grouped query (runs in 6 min 41 secs), vs 53MB for the aggregated query (runs in 5 min 31 secs), and the estimated row size is smaller when aggregated.

Can rationalise this? In my mind, the data that is being pulled is identical, plus there is extra computation for doing an unnecessary aggregation, so the aggregated query should be unquestionably slower, but the database engine has other ideas; it seems to be able to work more quickly when it needs to do unnecessary work :) Perhaps something to do with an inefficient query plan for the non-aggregated query? I would have thought looking at the actual execution plan might have made this apparent, but both plans look very similar.

Edit: More information, the "group by" query had two aggregations on it, a count of one of the columns, and an average of another one. I changed this so that it was just "1" instead of the count, and for the average, I changed it to be the expression within the average aggregate, since the aggregation effectively does not do anything.

View 2 Replies View Related

SQL Server Query, Are You Able To Bunch Together Child Table Fields.

May 24, 2008

Hi all,

I'm using SQL Server 2005 along side Visual Studio 2005, using VB to create a web application at work.
Now I'd like to bunch together some child fields in a gridview when displayed. Have a look at the attached pic to show what I would like.

Is this possible via a query or would I need to set something up on the gridview to do this?

Cheers,

Paul.

View 9 Replies View Related

Bp Server With The Lowest Price

May 25, 2004

bp server with the lowest price

Our company is a proffesional deticated bullet proof server and general server provider in china. If you're looking for a reliable and long term cooperation bp server provider, it deservers you to read our company file to know more about us.

we have the lowest price for bp server.

http://www.serverinchina.com/bpserverservice/index.htm


ICQ:226745581 MSN:dqkj#hotmail.com

ICQ:329211498 MSN:jiemie11#hotmail.com

Email: serverinchina#yahoo.com.cn
Website: www.mailinchina.com www.serverinchina.com

View 5 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







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