| Author |
Topic |
|
Rasta Pickles
Posting Yak Master
174 Posts |
 Posted - 2012-11-16 : 09:11:15
|
| Identifying all possible relationships between order ids?I will try to explain!I am working with a table that has pid, ptypeid, cid and ctypeid fields.Ignore the ptypeid & ctypeid fields for now.A pid can have multiple cids but those cids can also be pids as well and have their own set of cids.The aim is to roll up order values to the very top level pid.A simplified example:pid cid1---2----3----4In this case, order values from cids 2,3 and 4 would be rolled up to give an overall value for pid 1.That's the easy bit!Now consider this example:pid cid1---2----3----42---53---6----7In this case, order values from cids 2,3,4,5,6 and 7 would be rolled up to give an overall value for pid 1 because cid 5 is a child of pid 2 which itself is a child of pid 1 and cids 6 and 7 are children of pid 3 which itself is a child of pid 1.That's the key bit.Order values from cid 5 would NOT be rolled upto pid 2 because that one is not a top-level parent, it's a child of pid 1.That's the bit that's stumping me!I've tried to explain it as best I can, does anyone know of a way to accomplish what I am trying to achieve?I guess I would be looking to insert the results into a table which, in the above example, would look likepid cid1 21 31 41 51 61 7 I can then use this to do the necessary (hopefully).(For extra kudos, this problem has apparently been doing the rounds at my company for the past 14 months and three different people have had a crack at resolving it and none have succeeded!) |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-16 : 09:53:52
|
| What is in your data tables at the present time? Specifically, you show a blank against cid = 3 and 4. How do you know (programmatically) that they are child nodes of pid = 1? |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-16 : 10:20:08
|
| DECLARE @Table TABLE (pid int,cid int,amt int)INSERT INTO @Table (pid, cid, amt)VALUES ( 1,2,1 ), ( 1,3,1 ), ( 1,4,1 ), ( 2,5,1 ), ( 3,6,1 ), ( 3,7,1 ) ;WITH CTE AS ( SELECT PID,CID,AMT FROM @TABLE UNION ALL SELECT c.PID,T1.CID,T1.AMT FROM CTE CINNER JOIN @TABLE T1 ON T1.PID = C.CID ) SELECT C1.pID,C1.cid,C1.AMT FROM CTE C1WHERE NOT EXISTS (SELECT * FROM CTE C2 WHERE C1.PID = C2.CID)jIMEveryday I learn something that somebody else already knew |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-11-16 : 13:43:00
|
quote:
Originally posted by sunitabeck
What is in your data tables at the present time? Specifically, you show a blank against cid = 3 and 4. How do you know (programmatically) that they are child nodes of pid = 1?
Sorry, my bad......1 is the pid of cids 2,3 and 4, I should have made that clear.In the second example, 3 is the pid of cid 7. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-16 : 13:54:35
|
| Have you tried Jim's code? |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-11-16 : 15:35:55
|
| Yes, Jim's code is quite excellent for those seven records.For 44,000 I'm not sure it's entirely practical? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-16 : 15:47:56
|
| After 14 months of no one in your company able to solve it, why don't you at least try my solution?JimEveryday I learn something that somebody else already knew |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-11-17 : 02:40:58
|
quote:
Originally posted by jimf
After 14 months of no one in your company able to solve it, why don't you at least try my solution?JimEveryday I learn something that somebody else already knew
I will and thank you for taking the time to contribute, it is much appreciated.I assume there's no way to automate the population of the table?With 44,000 records, that's going to be an awful lof of VALUES to insert  |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-17 : 10:01:06
|
| You don't need to populate the table like Jim did - he was simply creating an example table to demonstrate and test the code. You need only the part that starts with ";WITH CTE AS ", assuming you have the equivalent of @TABLE in your database where the data is stored. |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-11-17 : 13:50:43
|
quote:
Originally posted by sunitabeck
You don't need to populate the table like Jim did - he was simply creating an example table to demonstrate and test the code. You need only the part that starts with ";WITH CTE AS ", assuming you have the equivalent of @TABLE in your database where the data is stored.
Ahhhh, see what the drawbacks are of being a SQL novice?That went straight over my head  |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-11-17 : 14:04:46
|
| Ok, just tried Jim's code:Msg 530, Level 16, State 1, Line 1The statement terminated. The maximum recursion 100 has been exhausted before statement completion.SQL does seem to have nothing but obstacles to overcome in my limited experience?Any workaround or can SQL not handle 44,000 records?Please don't tell me I have to resort to a grown-up language like VBA and use simple things like arrays? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-17 : 21:59:58
|
| Have you checked OPTION(MAXRECURSION) hint??? |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-11-18 : 02:36:21
|
Alas yes: set it to 32767 and gotMsg 530, Level 16, State 1, Line 1The statement terminated. The maximum recursion 32767 has been exhausted before statement completion. |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-11-18 : 07:30:01
|
All sorted! I've filtered the dross out of the original table and I have some output so thanks again to Jim  |
|
|
|
|
Identifying relationships between order ids?