SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Identifying relationships between order ids?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 11/16/2012 :  09:11:15  Show Profile  Reply with Quote
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 cid

1---2
----3
----4

In 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 cid

1---2
----3
----4
2---5
3---6
----7
In 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 like

pid cid
1 2
1 3
1 4
1 5
1 6
1 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!)

Edited by - Rasta Pickles on 11/16/2012 09:40:44

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/16/2012 :  09:53:52  Show Profile  Reply with Quote
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?
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/16/2012 :  10:20:08  Show Profile  Reply with Quote
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 C
INNER JOIN @TABLE T1 ON T1.PID = C.CID


)


SELECT C1.pID,C1.cid,C1.AMT
FROM CTE C1
WHERE NOT EXISTS (SELECT * FROM CTE C2 WHERE C1.PID = C2.CID)


jIM

Everyday I learn something that somebody else already knew
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 11/16/2012 :  13:43:00  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/16/2012 :  13:54:35  Show Profile  Reply with Quote
Have you tried Jim's code?
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 11/16/2012 :  15:35:55  Show Profile  Reply with Quote
Yes, Jim's code is quite excellent for those seven records.

For 44,000 I'm not sure it's entirely practical?
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/16/2012 :  15:47:56  Show Profile  Reply with Quote
After 14 months of no one in your company able to solve it, why don't you at least try my solution?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 11/17/2012 :  02:40:58  Show Profile  Reply with Quote
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?

Jim

Everyday 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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/17/2012 :  10:01:06  Show Profile  Reply with Quote
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.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 11/17/2012 :  13:50:43  Show Profile  Reply with Quote
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
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 11/17/2012 :  14:04:46  Show Profile  Reply with Quote
Ok, just tried Jim's code:

Msg 530, Level 16, State 1, Line 1
The 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?


Edited by - Rasta Pickles on 11/17/2012 14:06:26
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/17/2012 :  21:59:58  Show Profile  Reply with Quote
Have you checked OPTION(MAXRECURSION) hint???
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 11/18/2012 :  02:36:21  Show Profile  Reply with Quote
Alas yes: set it to 32767 and got

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 11/18/2012 :  07:30:01  Show Profile  Reply with Quote
All sorted! I've filtered the dross out of the original table and I have some output so thanks again to Jim
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000