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.
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...
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:
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
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.
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
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
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.
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:
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.
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
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.
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
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.
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.)
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
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
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:
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?
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.
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)
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.
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 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
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
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.
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
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.
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.
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
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?
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
