Recursive Multi-Level Query Using CTE. Attn: Experts!
Mar 21, 2007
I am attempting to do the following....
I have standard tree setup. The tree can be up to 4 nodes deep. User permissions may be assigned at any level in the tree. Any
permission should cascade down the tree to the lowest child node.
For example, if a user had a role of 1 for the root node (101), the sql should return:
OrgID RoleID
101 1
102 1
103 1
etc...
My table structure is as follows....
Org
OrgID ParentID
101 Null
102 101
103 101
105 102
106 102
107 105
108 105
109 106
110 106
111 106
UserOrgRole
UserID OrgID RoleID
User1 101 1
User1 102 2
User1 103 2
User1 107 2
User2 101 1
User3 106 3
etc...
What I would like to retrive from the above table data is....
OrgID RoleID
102 2
105 2
106 2
107 2
108 2
109 2
110 2
111 2
This is so because all the nodes (except for 101 and 103) are somehow decedent from the 102 node and 102 has a roleid of 2. I am only concerned with the RoleID 2 and User1.
I have worked for two days trying to figure out how to do this. I am not a DBA or SQL expert by any means. I cannot seem to figure
out how to traverse multiple levels of the tree. I have been using the new CTE and made some progress, but I think I reached my
plateau and haven't been able to get any further.
If someone could help me, I would be forever in your debt! I am really starting to get very frustrated and I know there are some of
you experts out there that would know exactly what to do.
thanks!
View 19 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
Oct 3, 2005
hello guysi am using a table that its secondary key connected to its primary key...and as sql server 2000 doesnt allow cascade delete fore such,i had to write a trigger myselfso i wrote the following triggerCREAT TRIGGER nameON tableFOR DeleteASBEGINIF @@ROWCOUNT >0Delete from table where table.parentID in (select sortID from deleted);ENDthen i went to the table and i tried to delete...and it gave me an error....that there are records that have there parentID= sortID of the table i am trieng to delete...so i deleted the relationship...and kept the triggerand now ...when i delete one...it deletes one level down....but not more....i mean when i delete sortID=4it deletes all the records that has parentID=4...and NOT more..whereas my aim was to have it recursive not to have records lost in my databasehope i explained good as much as i hope to find an answer soon...a clear one...and thanks in advanced...
View 3 Replies
View Related
Aug 28, 2006
Hello,
I am using SRS 2000 and was wondering if there is any way to have more than 1 level in my document map.
Here is the scenario:
I have a report that displays
Customer
Contract 1
Project 1
Project 2
Contract 2
Project 1
Project 2
etc...
I would like the document map to include a list of Customers, Contracts and Projects as some people who view the report know the contract code they are interested in, others know the project code and others know the customers.
However, when I set it up I get a list with them all jumbled together:
Report Name
Customer 1
Project 1
Customer 2
Contract 1
etc...
I would like the document map to have separate levels for each of them:
Report Name
Customer
Customer 1
Customer 2
Contract
Contract 1
Contract 2
Project
Project 1
Project 2
Does anyone know if this is possible?
Thanks,
Paul.
View 7 Replies
View Related
Jul 23, 2005
Hi all,What I am trying to do is generate a stored procedure that is desiredto output XML in this type of format<Parent Device><Device><Device ID>1</DeviceID><ChildRegister><ChildRegisterID>22</ChildRegisterID></ChildRegister></Device><Device><Device ID>2</DeviceID><ChildRegister><ChildRegisterID>23</ChildRegisterID></ChildRegister></Device></Parent Device>The area of concern is the child register, the XML being generateddisregards the Device the ChildRegister belongs to and always places itas elements of the last device.<Parent Device><Device><Device ID>1</DeviceID></Device><Device><Device ID>2</DeviceID><ChildRegister><ChildRegisterID>23</ChildRegisterID></ChildRegister><ChildRegister><ChildRegisterID>22</ChildRegisterID></ChildRegister></Device></Parent Device>I am trying to produce XML like the first one I described and have yetto discover a way of associating the ChildRegister with the parentDevice in XML. I am not sure if it is a limitation of SQL Server, or ifmy implementation is incorrect. If anyone could post hints orsolutions, I would greatly appreciate it.A shortened version of the stored procedure is belowCheers :)AlvinSELECT1AS TAG,NULL AS PARENT,NULL AS [Device!2!DeviceID!element],NULL AS [ChildRegister!3!RegisterID!element]FROM udetails INNER JOINDetail ON udetails.ID = Detail.IDWHERE (uDetails.JobID = @ID)UNION ALLSELECT2 AS TAG,1 AS PARENT,TempTable.DeviceIDAS [Device!2!DeviceID!element],NULL AS [ChildRegister!3!RegisterID!element][color=blue]>From #Temp as TempTable INNER JOIN[/color]device ON TempTable.DeviceID = device.DeviceIDUNION ALLSELECT3 AS TAG,2 AS PARENT,NULL AS [Device!2!DeviceID!element],RegisterID AS [ChildRegister!3!RegisterID!element]FROM #Temp t INNER JOINregister ON t.DeviceID =register.DeviceIDFOR XML EXPLICIT
View 1 Replies
View Related
Sep 7, 2007
this is so stupid and simple and I am annoyed over having to spend so much on this silly simple stuff.
I am sure I am just making a silly mistake.
I am trying to remove records from one table. The table holds 19000 something records.
To determine WHICh records to delete, I have another table that contains the 45 I want to delete.
So I wrote this very simple query
Delete from tbl_X
where tbl_X.FieldA = tbl_Y.FieldA;
The message I get is:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tblY.FieldA" could not be bound.
Please tell me I am stupid!
Thanks!
View 8 Replies
View Related
Jul 23, 2015
SSRS 2008 R2
I have two level of grouping  Example:
:::HEADER::: Â Â Â
A [GROUPING 1]
B[GROUPING 2]
C[GROUPING 2]
When I apply Page Break
:::HEADER::: Â Â Â
A [GROUPING 1]
        B[GROUPING 2]
:::HEADER::: Â Â Â
A [GROUPING 1]
        C[GROUPING 2]
[URL]
View 3 Replies
View Related
Sep 7, 2006
Hi chaps,
I have the following SQL query (SQL 2005).
Its basically retrieving some values using simple joins.
However there appears to be a problem with the LEFT OUTER JOIN:
"LEFT OUTER JOIN DDDispatchedOrder ON (OrderLineItemTransaction.OrderLineItemTransaction ID = DDDispatchedOrder.OrderItemTransactionID)
"
When I try to compile the code i Get :
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "OrderLineItemTransaction.OrderLineItemTransactionI D" could not be bound.
Any help would be appreciated.
Cheers
Bal
SELECT
ord.orderDate,
cc.forename + ' ' + cc.surname person,
prod.description,
oli.noofitems,
deladdr.housenameno + ' ' + deladdr.addressLine1 + ' ' + deladdr.addressLine2 + ' ' + deladdr.city + ' ' + deladdr.postcode + ' ' + deladdr.county + ' ' + deladdr.country deladdress
FROM
product prod,
OrderLineItem oli,
[Order] ord,
OrderTransaction ordT,
OrderLineItemTransaction oliT,
CustomerContact cc,
Customer cust,
DDDispatchedOrder dd,
address deladdr,
address invaddr
LEFT OUTER JOIN DDDispatchedOrder ON (OrderLineItemTransaction.OrderLineItemTransaction ID = DDDispatchedOrder.OrderItemTransactionID)
WHERE
prod.productID = oli.productID:eek:
AND ord.orderID = oli.orderID
AND ord.orderID = ordT.orderID
AND oliT.orderlineitemID = oli.orderlineitemID
AND cc.customercontactID = ord.customercontactID
AND cc.customerID = cust.customerID
AND ord.invoiceaddressID = invaddr.addressID
AND ord.deliveryaddressID = deladdr.addressID
AND ordT.dispatchTypeID = 2
View 7 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
Jul 15, 2002
I need to determine the differences between two tables with the same structure. The primary key for these tables would be a combination of all the columnhs. They use the ID field to join all their records. (i know ID is a reserved word - the client made the tables) Below is the structure of the tables.
TableA
and
TableB
----------
ID int
ComBank int
inboth int
EqorMA int
TranType int
I need to know the rows that Are different in TableA from TableB.
Any help would be appreciated.
Thanks
James
View 2 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
Feb 22, 2001
Hi,
I am unable to understand how can I create a new filegroup?
I know how to move certain text/ntext/images column to a new file group - thru design table but my problem is how to create a new filegroup?
Do I have to use Alter Table/Alter Database for this or there is any other better way?
TIA
Jai.
View 2 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
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
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
Aug 16, 2007
Hi Folks - Well this is going to be a fun ride. I have a simple table that I need to do some slightly not so simple logic on. I am using SQL2005 and this happens in a stored proc. I need to do this in straight T-SQL and w/o using a cursor, you can use a CTE if needed.
ID
PARENTID
CAT
CATRANK
THEVALUE
1
1
A
0
11
2
1
B
1
22
3
1
C
2
33
4
2
A
0
44
5
2
B
1
55
6
2
C
2
66
7
3
A
0
12
8
3
B
1
13
9
4
A
0
14
10
4
B
1
15
11
4
C
2
16
I need to a variation on select cat, sum(thevalue) from TABLE1 group by cat
which results in
CAT
THEVALUE
A
81
B
105
C
115
the wrinkle is that each item in the group by (A,B,C) needs to consider 1 row from each parentid. in other words if the system is calculating the C row, and a parentid is missing a C entry then the system needs to use the B entry for that parentid.
so to calculate the A row we simply do. this is simple because there is an A row for every parentid
PARENTID
CAT
THEVALUE
1
A
11
2
A
44
3
A
12
4
A
14
SUM
81
the C row is the tricky part. there is no C for parentid 3, so in that case I need to use the next lower ranked item, which is a B
PARENTID
CAT
THEVALUE
1
C
33
2
C
66
3
B
13
4
C
16
SUM
128
so after all is said and done the final result needs to look like this
CAT
THEVALUE
A
81
B
105
C
128
View 3 Replies
View Related