Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-03-08 : 07:30:42
|
As a temp fix until the software can be changed I have created a sproc to repair a relationship table that is being built incorrectly.I can run it as a while loop, but because of the tens of thousands of rows it takes a long time. I was wondering if the below could be done by a case statement to improve performance?basically, it's a parent/child/grandchild/great .. etcThere three key fields are person.NameID(varchar) person.ParentID(varchar), Person.Gen(int) - (their generation) Then there is the relationship table PrsonsRelfields PrsonsRel.childID & PrsonsRel.Rel(relation)there should be entries for all persons in the childID and in PersonsRel it should show the father, another entry along below for grandfather if they have one, another below great grand etc or a --- if they are a generation 1 with a NULL for person.ParentIdI.E a third generation (has father and grand father)PrsonsRel.childID PrsonsRel.RelBrian -- /*(all have an entry like this*/)Brian John234 /*(Father)*/Brian Dave5674 /*(GrandFather)*/Because the table is designed to only link the child to his immediate parent, I can only rebuild the relation table by looping through from youngest to oldest.Any ideas if/how this could be put into a case statement? or is a loop the only way to go?As mentioned, this is only as a temp fix so any table changes would not be worth it.Cheers |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-08 : 07:44:38
|
You can join the table onto itself to get the parent and grandparent (and then use that to update the table if necessary). Code below is psuedocode - if you post DDL and sample data, it can be made more specific:;WITH cte AS( SELECT c.*, p.name, gp.name FROM YourTable c LEFT JOIN YourTable p -- for parent ON p.nameId = c.ParentId LEFT JOIN YourTable gp -- for grandparent ON gp.nameId = p.ParentId)SELECT * FROM cte;-- or your update statement here using the cte If you need help in posting DDL and sample data in a consumable format, these links might help:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-03-08 : 09:18:25
|
So that's the benefit of having Cte's as being self referencing!Thanks James, I'll have a go from here out (learn by doing!) and see how it goes.Much appreciated. Robquote: Originally posted by James K You can join the table onto itself to get the parent and grandparent (and then use that to update the table if necessary). Code below is psuedocode - if you post DDL and sample data, it can be made more specific:;WITH cte AS( SELECT c.*, p.name, gp.name FROM YourTable c LEFT JOIN YourTable p -- for parent ON p.nameId = c.ParentId LEFT JOIN YourTable gp -- for grandparent ON gp.nameId = p.ParentId)SELECT * FROM cte;-- or your update statement here using the cte If you need help in posting DDL and sample data in a consumable format, these links might help:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
|
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-03-08 : 10:30:09
|
James,The problem I had when first trying to do this as a non loop update, was that the the generation would vary in regard to how many grand, or great grands there were.Below is some sample code if this makes more senseIF OBJECT_ID('Persons', 'U') IS NOT NULL TRUNCATE TABLE PersonsELSE CREATE TABLE Persons ( ChildID varchar(10) NOT NULL ,ParentID varchar(10) NULL ,Gen int NOT NULL )GOIF OBJECT_ID('Persons', 'U') IS NOT NULL TRUNCATE TABLE PrsonsRelELSE CREATE TABLE PrsonsRel ( ChildID varchar(10) NOT NULL ,Rel varchar(10) NOT NULL )GOINSERT INTO Persons--1st generation BrianValues ('Brian', NULL, 1)INSERT INTO Persons--2nd generation John (1st son)Values ('John', 'Brian', 2)INSERT INTO Persons--2nd generation wayne (2nd son) Wayne has no sons, ends here with him)Values ('Wayne', 'Brian', 2)INSERT INTO Persons--3rd generation Dave - Dave has no sons - ends here.Values ('Dave', 'John', 3)INSERT INTO Persons--1st generation Rees - No sons ends hereValues ('Rees', NULL, 1)INSERT INTO Persons--1st generationValues ('Craig', NULL, 1)INSERT INTO Persons--2nd generation Keith Values ('Keith', 'Craig', 2)INSERT INTO Persons--3rd generation FredValues ('Fred', 'Keith', 3)INSERT INTO Persons--4th generation AndyValues ('Andy', 'Fred', 4)INSERT INTO Persons--4th generation steveValues ('Steve', 'Fred', 4) I trying to build the relationship table to display the below results without using a loop if possible.SELECT r.*, p.gen FROM PrsonsRel r JOIN Persons p ON r.ChildID = p.ChildID Order by r.childID, p.gen ascChildID Rel gen---------- ---------- -----------Andy --- 4Andy Fred 4Andy Keith 4Andy Craig 4Brian --- 1Craig --- 1Dave --- 3Dave John 3Dave Brian 3Fred --- 3Fred Craig 3Fred Keith 3John --- 2John Brian 2Keith --- 2Keith Craig 2Rees --- 1Steve --- 4Steve Fred 4Steve Keith 4Steve Craig 4Wayne --- 2Wayne Brian 2 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-08 : 10:50:33
|
Can you post the data to populate the PrsonsRel table also? |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-03-08 : 11:21:21
|
quote: Originally posted by James K Can you post the data to populate the PrsonsRel table also?
INSERT INTO PrsonsRelValues ('Andy', '---')INSERT INTO PrsonsRelValues ('Andy', 'Fred')INSERT INTO PrsonsRelValues ('Andy', 'Keith')INSERT INTO PrsonsRelValues ('Andy', 'Craig')INSERT INTO PrsonsRelValues ('Brian', '---')INSERT INTO PrsonsRelValues ('Craig', '---')INSERT INTO PrsonsRelValues ('Dave', '---')INSERT INTO PrsonsRelValues ('Dave', 'John')INSERT INTO PrsonsRelValues ('Dave', 'Brian')INSERT INTO PrsonsRelValues ('Fred', '---')INSERT INTO PrsonsRelValues ('Fred', 'Craig')INSERT INTO PrsonsRelValues ('Fred', 'Keith')INSERT INTO PrsonsRelValues ('John', '---')INSERT INTO PrsonsRelValues ('John', 'Brian')INSERT INTO PrsonsRelValues ('Keith', '---')INSERT INTO PrsonsRelValues ('Keith', 'Craig')INSERT INTO PrsonsRelValues ('Rees', '---')INSERT INTO PrsonsRelValues ('Steve', '---')INSERT INTO PrsonsRelValues ('Steve', 'Fred')INSERT INTO PrsonsRelValues ('Steve', 'Keith')INSERT INTO PrsonsRelValues ('Steve', 'Craig')INSERT INTO PrsonsRelValues ('Wayne', ' ---')INSERT INTO PrsonsRelValues ('Wayne', 'Brian') Cheers |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-08 : 14:23:36
|
I didn't quite follow the relationship logic, but the following query gives you the output you posted:SELECT DISTINCT r.*, p.GenFROM PrsonsRel r INNER JOIN Persons p ON p.ChildId = r.ChildIdORDER BY ChildId; |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-03-08 : 16:27:41
|
James,Thanks for your response but I'm trying to build that table not query it.So, assume PrsonsRel is empty or has been truncated, or because of the software fault is incorrect and needs updating. I need to then populate it with the correct information from the Persons table.Every ChilID should have an entry is in PrsonsRel for it's father, and it's fathers father(s)so if a child was Fourth generation, i.E it has a father, it's father has a father record(it's grand father) and it's fathers father has a father record (it's great grand father)there would be 4 entries for it in PrsonsRelChildID Rel ---------- ---------- Andy --- -- all it's have this entry once Andy Fred -- it's father's, father's father. Andy Keith -- it's fathers father Andy Craig --it's father There is no limit really for how long this can go on, depends on the data. I have created a script that loops through each generation and builds these records based on a MAX(gen) so the script neevr has to be changed. However the performance is very poor and usually a case statement instead of a while loop is better?I can't figure out how to create this though without constantly amending it if newer generations are added. Does this make sense?quote: Originally posted by James K I didn't quite follow the relationship logic, but the following query gives you the output you posted:SELECT DISTINCT r.*, p.GenFROM PrsonsRel r INNER JOIN Persons p ON p.ChildId = r.ChildIdORDER BY ChildId;
|
|
|
|
|
|
|
|