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
ADVERTISEMENT
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
Feb 25, 2008
Hello, I am hoping someone can help me in this. I am looking to write a stored procedure that will return the heirarchy of an organization. I will display how the heirarchy might look and then list the tables involved.
John Smith
- Jacob Jones
- Lisa Thompson
- Samuel Barber
- Paul Smith
- John Jackson
Ok, so Jacob, Lisa, an Samuel report up to John Smith. Paul and John Jackson report up to Samuel Barber.
Here are the tables:
Users holds the user_id, first_name, last_name, and reports_to_user_id.
User_Roles holds the user_id, role_type_id
Role_Types holds the role_type_id, and the type (which could be Administrator, Standard, Guest) for example. In addition, Role_Types also has ranking which must be taken into consideration as well. 1 being the top rank and 9 being the lowest.
Thanks very much in advance,
Saied
View 12 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Apr 18, 2008
I have a recursive query, using common table expressions, like this:
WITH TaskHierarchy (GUID, ParentGUID, Title, Complete, HierarchyLevel)
AS
(
SELECT GUID, ParentGUID, Title, Complete, 1 HierarchyLevel
FROM Task
WHERE ParentGUID = @GUID
UNION ALL
SELECT t.GUID, t.ParentGUID, t.Title, t.Complete, th.HierarchyLevel + 1 HierarchyLevel
FROM Task t
INNER JOIN TaskHierarchy th
ON t.ParentGUID = th.GUID
)
SELECT (COUNT(*) - SUM(CAST(Complete AS INT))) Outstanding FROM TaskHierarchy
The result is a number. I need access to this number. Ideally, I would like to store it in a variable, but anything would work as long as I can access it after the query.Anyone know of a way?
View 1 Replies
View Related
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
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
Sep 13, 2007
I need a statement that can return a hierarchy of folders. There are a number of good articles out there describing how to do this in SQL 2005 with a Common Table Expression (CTE) that references itself under a UNION ALL. Yet every example I come across involves a self-referencing table. Unfortunately the architect of my project did not use that simple structure. Instead, we have a Folder table and Folder Properties table. Creating a child Folder involves adding a row in the Properties table that links to a parent. Here's the statement I came up with:
WITH Folders (ID, ParentID, FolderName, FolderDepth)AS ( SELECT FOLDER_ID, NULL, FOLDER_NAME, 0 FROM ZZ_FOLDER WHERE FOLDER_ID = 1 UNION ALL SELECT F.FOLDER_ID, Folders.ID, F.FOLDER_NAME, Folders.FolderDepth + 1 FROM ZZ_FOLDER F INNER JOIN ZZ_FOLDER_PROP FP ON FP.FPROP_FOLDER_ID = F.FOLDER_ID WHERE FP.FPROP_LINK_ID = Folders.ID )SELECT * FROM Folders
The initial statement brings back the top of the folder structure. The recursive statement brings back Folder rows that have an entry in their Property rows for the parent. Yet when I try to execute this, I get:
Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.ID" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.ID" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.FolderDepth" could not be bound.
It sounds like all the recursive references to the CTE are unavailable. What am I doing wrong? Would welcome any suggestions, too. I realize this isn't a T-SQL specific question, more SQL in general, but I didn't see a more relevant forum. What follows are some tables/data to run the above statement against.
-- Create Folder TablesCREATE TABLE ZZ_FOLDER ( FOLDER_ID INTEGER NOT NULL PRIMARY KEY, FOLDER_NAME VARCHAR(50))CREATE TABLE ZZ_FOLDER_PROP ( FPROP_ID INTEGER NOT NULL PRIMARY KEY, FPROP_FOLDER_ID INTEGER NOT NULL, FPROP_LINK_TYPE VARCHAR(50), FPROP_LINK_ID INTEGER NOT NULL)GO-- Populate Folder TablesINSERT INTO ZZ_FOLDER VALUES (1, 'Top Level')INSERT INTO ZZ_FOLDER VALUES (2, 'Second Level A')INSERT INTO ZZ_FOLDER VALUES (3, 'Second Level B')INSERT INTO ZZ_FOLDER VALUES (4, 'Second Level C')INSERT INTO ZZ_FOLDER VALUES (5, 'Third Level A1')INSERT INTO ZZ_FOLDER VALUES (6, 'Third Level A2')INSERT INTO ZZ_FOLDER VALUES (7, 'Third Level A3')INSERT INTO ZZ_FOLDER VALUES (8, 'Third Level B1')INSERT INTO ZZ_FOLDER VALUES (9, 'Third Level C1')INSERT INTO ZZ_FOLDER VALUES (10, 'Third Level C2')INSERT INTO ZZ_FOLDER VALUES (11, 'Fourth Level A3A')INSERT INTO ZZ_FOLDER_PROP VALUES (1, 2, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (2, 3, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (3, 4, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (4, 5, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (5, 6, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (6, 7, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (7, 8, 'Folder', 3)INSERT INTO ZZ_FOLDER_PROP VALUES (8, 9, 'Folder', 4)INSERT INTO ZZ_FOLDER_PROP VALUES (9, 10, 'Folder', 4)INSERT INTO ZZ_FOLDER_PROP VALUES (10, 11, 'Folder', 7)GO/*Top Level Second Level A Third Level A1 Third Level A2 Third Level A3 Fourth Level A3A Second Level B Third Level B1 Second Level C Third Level C1 Third Level C2*/
View 3 Replies
View Related
Mar 17, 2007
My Table Structure
Category_ID Number
Parent_ID Number <----Category_ID reports to this colum
Category_Name Varchar....
MY QUERY <---I replaced the query above with my data
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy
My OUTPUT============
All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.
Can you help me please?
View 12 Replies
View Related
Mar 12, 2013
Information stored within the database is as follows, although the number of sub levels could easily grow :
PageID - Title - ParentID
1 - Title1 - 0
2 - Title2 - 0
3 - Title3 - 1
4 - Title4 - 1
5 - Title5 - 4
Required outcome :
[+] Title1
.....[+] Title3
.....[+] Title4
..........[+] Title5
[+] Title2
View 3 Replies
View Related
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
Jul 20, 2005
I have a database which stores information about organisms collectedduring sediment toxicology research. For each sample, organisms insediment are collected and identified taxonomically (Order, Family,Genus, Species).Taxonomy lookup information in the database is stored in a recursivetable in the form:TSN (taxa serial number)Rank (Order, Family, Genus, Species)NameParent_TSN (related Taxa at higher taxonomic level)When the number of a particlar organism collected is entered into thedatabase, the count is stored along with the lowest level TSN theorganisms were identified to.Okay - now the problem. Depending on the type of analysis being done,a user may want organism counts at the lowest level, or rolled up to ahigher taxonomic level (usually Family). Can I write a recursivefunction which will cycle through the Taxonomy database, and providethe name of the organism at the appropriate taxonomic level? Is this areasonable approach with regard to speed and efficiency?Something Like:SELECT sample_id, 'Get Name Function(Rank, TSN)', Sum([count]) ASNoTaxa FROM dbo.tblbenthicResults could then be grouped and summed on the Name, to summarisedata for each sample/taxa.Is this a reasonable approach? Or is there a better one? Did I explainthe problem well enough?Thanks in advance,Tim
View 6 Replies
View Related
Jun 11, 2006
Hi
I wrote a simple sql query to get the shortest path length from node 1 to all the other nodes. Since there's a loop in the graph, I want to prevent it from going back to some nodes it has expanded before.
I got the following error:
Msg 253, Level 16, State 1, Line 2
Recursive member of a common table expression 'CTE_Sample' has multiple recursive references.
It is referring to "and Table1.t NOT IN (select fr from CTE_Sample)"
Can somebody help me to solve it?
btw. How to use the UNION, EXCEPT or INTERSECT operators when doing the recursive join? It seems I must use UNION ALL.
Thanks!
drop table Table1
CREATE TABLE Table1 ( fr int, t int)
INSERT Table1 VALUES (1, 2)
INSERT Table1 VALUES (2, 3)
INSERT Table1 VALUES (3, 4)
INSERT Table1 VALUES (1, 3)
INSERT Table1 VALUES (1, 4)
INSERT Table1 VALUES (4, 5)
INSERT Table1 VALUES (4, 2)
GO
WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
and Table1.t NOT IN (select fr from CTE_Sample)
)
SELECT CTE_Sample.t, min(CTE_Sample.level)
FROM CTE_Sample
group by CTE_Sample.t
View 2 Replies
View Related
