Transact SQL :: Recursive Query To Find Child Of A Parent Until Last Leaf
Oct 22, 2015
I need to write recursive query to find child of a parent until the last leaf. Below is my code.
;WITH Parent AS(
SELECT [ParentID],Value
FROM[DynamicColsValues_TP1]
WHEREValue IS null
UNION ALL
SELECT t1.[ParentID],T1.Value, FROM DynamicColsValues_TP1 t1 INNER JOIN Parent t2
ON t1.[ParentID]=t2.[ParentID]
)
SELECT * FROM Parent option (maxrecursion 0)
When I execute this code. It is returning me millions of rows. Whereas i have only 20 rows in a table max 40 rows it should return.
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date) declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date) insert @parent select 1, '1/1/2015', '12/31/2015' insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate) 2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate) 3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
I am using the following code to get the next immediate parent of a customer in the hierarchy and it works fine. However, it works only for one customer at a time (i made this a scalar function and I pass the customer id as shown below).
How can I modify this so that i can get each customer and its next immediate parent (all of them in one shot in a single data set)?
WITH my_cte (CustomerID, ParentID, Level) AS ( SELECT CustomerID, ParentID, 0 AS Level FROM [dbo].MyTable WHERE CustomerID = @CustomerID
UNION ALL SELECT CustomerID, ParentID, Level + 1 FROM [dbo].MyTable e INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID )
select CustomerID from my_cte WHERE Level <> 0 AND Level = (SELECT MIN(Level) FROM my_cte WHERE Level <> 0)
insert into Hier select 'subramanium','Manickam' union all select 'subramanium','Munuswamy' union all select 'Munuswamy','senthil' union all select 'Munuswamy','sasi' union all select 'Munuswamy','uma' union all select 'manickam','vijay' union all select 'manickam','bhavani' union all select 'manickam','dhanam' union all select 'uma','varsha'
Delete from Hier where child='uma'
I tried:
select parent from Hier where parent not in(select Child from Hier) and parent <> 'subramanium' Getting resultset as: parent ====== uma
I need to know whether my select statement is correct or not,if its correct,how to write the same in CTE?
1. For U_ID = 1, we have two diffrent U_NM and for one U_NM we have 2 child data. Need to show parent data order by U_ORD and need to show child data within their parent order by U_DT
2. For U_ID = 2, we don't have child data, hence need to show data order by U_ORD only
SELECT 1 AS U_ID, 'Plan' AS U_NM, '06/01/2015' AS U_DT, 1 AS U_ORD, NULL AS P_U_NM UNION ALL SELECT 1, 'Cust Plan 2', '06/05/2015', 0, 'Plan' UNION ALL SELECT 1, 'Cust Plan 1', '06/10/2015', 0, 'Plan' UNION ALL SELECT 1, 'Design', '06/15/2015', 2, NULL UNION ALL SELECT 2, 'Plan', '06/20/2015', 1, NULL UNION ALL SELECT 2, 'Design', '06/25/2015', 2, NULL
We have 2 different U_ID (1, 2) and I want a SELECT query to display,
1. For U_ID = 1, we have 2 parent U_NM (Design & Plan) and Plan having 2 child (Cust Plan 1 & Cust Plan 2). 2. I want to display parent U_NM ORDER BY U_ORD 3. If any parent having child element, then need to show immediately under that parent and ORDER BY U_DT 4. For U_ID = 2, we don't have any child, hence display ORDER BY U_ORD
I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.
Link: [URL]
--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent
--- Query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase] FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID] INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId] WHERE [Patient].PracticeID = '55';
--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase] FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID] INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID] WHERE [Patient].PracticeID = '55';
how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2
The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using -
SELECT Cols FROM Table Names FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL
This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format -
CREATE TABLE #tree ( Childid varchar(20), Parentid varchar(20) ) INSERT INTO #tree (Childid,ParentId) SELECT '123' , null UNION ALL SELECT '456' , '123' UNION ALL SELECT '789' , '456' UNION ALL SELECT '870' , '456' UNION ALL SELECT '985' , '870';
At the 2nd level, the calculated measure will only be correct if it is Averaged. And at the 1st level, the calculated measure will only be correct to take these Average value from 2nd level and Sum then up together.
Level 1 Level 2 A X1 X2 ================= (Avg of X1 + X2) B Y1 Y2 Y3 ================= (Avg of Y1 + Y2 + Y3) =================================================== (Sum of (Avg of X1 + X2) + (Avg of Y1 + Y2 + Y3) )
Currently, Instead of summing all the 3 averaged value, it is averaging against all the items like,
(Avg of X1 + X2 + Y1 + Y2 + Y3)
My MDX currently looks something like that
[Measure].[Value] / [Dimension].[Attribute.count] -> so i can get the avarage at the 2nd level but it doesn't require on the 1st level but retaining this value.
How can i do an average on the leaf level and using these average values to sum at parent level?
I want to retrive the output in a single SQL query.
I had two columns in a table; I used some values of column1 in column2. I want to identify all the column1 vlaues in Left Hand side and column2 values in Right Hand side, with a single query.
example:
MasterTable
ParentRow ChildRow --------- -------- A B C D E F G C H E M G
Output required:
ParentRow ChildRow --------- -------- A B C D E F G D (C mapped tfrom D) H F M D (G mapped from C, and C mapped from D)
To get the above output, I written query using WHILE...[BREAK|CONTINUE] concept until I get @@rowcount become zero.
I am looking for better performace and to get the output in a single query, instead of multiple passes.
Can any one suggest good tools and articles on performance tuning techniques?
I have two SSIS packages in a project, one calling the other. The parent package works fine in my local mechine. After they are deployed to the production, I schedeul jobs to run the packages in the SqlServer. The child package works fine if I run it alone, but the parent package could not find its child package if I run the parent package . As I checked, all xml config files and the connection string pointing to the child package were set correctly. It seems the parent package did not use the xml config file. Can someone help me? Thanks in advance.
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
Request ID Parent ID Account Name Addresss 1452 1254789 Wendy's Atlanta Georgia 1453 1254789 Wendy's Norcross Georgia 1456 1254789 Waffle House Atlanta Georgia
I'm trying to find it difficult to use recursice CTEs or better solution for a special request below. There are two tables 1) @sizes serves as a lookup or reference for right drive and 2) @test is sample data with different sizes. Assume that I want to evenly distribute the drive letters from table1 to table2 by checking the size available.
E.g.: for the first record in @test; id = 1 where the size is 50 and it fits in Y: drive -- left over space in Y: = 50
id=2, size 2.5, space available from left over = 50 - 2.5 = 47.5 which again fits into Y: id = 3, size 51, cannot in fit in Y: drive as there is enough space in Y: to allocate (51 > 47.5)
so pick the next drive check on availability again
DECLARE @sizes TABLE (id TINYINT, size DECIMAL(5,2), drive VARCHAR(3)) INSERT INTO @Sizes SELECT 1,100.00,'Y:' UNION ALL SELECT 2,80.85,'Z:'
[Code] ....
-- My output should look like
col1 , val , path A 50 Y: B 2.5 Y: C 51 Z: D 2.6 Y: E 52 Z: F 2.7 Y:
I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?
Code: SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.[Parent ID] FROM People_tbl RIGHT OUTER JOIN Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID] WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT Student.StudentSurname + ', ' + Student.StudentForename AS Fullname, HomeworkLogLine.HomeworkLogLineTimestamp, HomeworkLog.HomeworkLogDescription, ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
I've been able to get this select query to work, but I'm not sure how to modify it to turn it into a DELETE query:
USE QSCTestENG select p.[testid], COUNT(c.[testid]) FROM [dbo].[tblTestHeader] p left outer join [dbo].[tblTestMeasurements] c ON p.[testid]=c.[testid] where p.[model] = 'XPPowerCLC125US12' group by p.[testid] having COUNT(c.[testid]) <>48;
I have a hierarchical structure for mapping products to categories, categories go 3 levels deep (depth is defined in articlegroups.catlevel, 0 being the main category and traversing down to lower category level 2). Also, a product may be in more than 1 category(!).
product details are stored in `[products]` articlegroups are defined in `[articlegroups]` and the mapping of the products to the articlegroups are defined in `[products_category_mapping]`
Now, I want to retrieve index the full category path for each item, so with the data provided below, I'd expect these 2 rows as a result:
Now I can get the separate fields via a statement like this:
SELECT ga.slug_nl as slug_nl_0 FROM articlegroups ga INNER JOIN products_category_mapping pcm ON pcm.articlegroup_id=ga.id INNER JOIN products gp on gp.id=pcm.artikelid WHERE gp.id=2481446
hi, i have two tables i want the identity value of the parent table to be inserted into the chile table here is my code,but i don't know why it isn't working ! protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string pcontent = TextBox1.Text; string data = TextBox2.Text; addtopic(pcontent,connectionString); addfile(data, connectionString); } public void addtopic(string subject,string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) { SqlCommand command = new SqlCommand("INSERT INTO parent" + "(content)" + "Values(@content)", connection); command.Parameters.Add("@content", SqlDbType.Text).Value = subject; connection.Open(); command.ExecuteNonQuery(); } } public void addchild(string name, string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) {Guid id = Guid.NewGuid(); SqlCommand commandd = new SqlCommand("INSERT INTO child" + "(parentid,data,uniqueid)" + "Values(@@IDENTITY,@data,@uid)", connection); commandd.Parameters.Add("@data", SqlDbType.NVarChar, 50).Value = name; commandd.Parameters.Add("@uid", SqlDbType.UniqueIdentifier).Value = id;
