Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

problem in logic for finding a Recursive Cte Expand / Collapse
Author
Message
Posted Saturday, May 3, 2014 4:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
hi,
hope everybody doing good,
here is my problem this is my data
DECLARE @Tbl AS TABLE (Mainid INT, Referenceid INT)
INSERT INTO @Tbl
VALUES
(1,1), (1,2), (1,3),
(2,1), (2,3), (3,3),
(3,2), (4,3), (4,4),
(5,1), (5,3), (6,5),
(6,6), (7,6), (7,7),
(8,6), (8,7), (9,4),
(9,9), (9,10), (9,12)
SELECT * FROM @Tbl

For a particular reference id i try to get an output of it's sibling in each Mainid group and also the it's relative siblings also
well if i gave any one value of the following set, then i want the entire set as output
1
2
3
4
9
10
12
(i.e) the reference id relate with one another in each mainid group
so far this is my try not success yet,
WITH base as(
SELECT t.Mainid, t.Referenceid, ROW_NUMBER() OVER ( ORDER BY t.mainid,t.referenceid) Rn FROM @tbl t
),rec AS(
SELECT t.Mainid, t.Referenceid, rn
FROM Base T
WHERE t.Referenceid = 1
UNION ALL
SELECT t.Mainid, t.Referenceid, t.rn
FROM Base T
INNER JOIN rec ON T.Referenceid =rec.Referenceid AND T.rn >rec.rn
)
SELECT mainid, referenceid FROM rec

any help will be helpfull to me




Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
Post #1567252
Posted Sunday, May 4, 2014 5:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 2,532, Visits: 7,069
thava (5/3/2014)
hi,
hope everybody doing good,
here is my problem this is my data


The problem is in the data, each node can only have one parent!
Post #1567329
Posted Sunday, May 4, 2014 7:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
Yes I know it, that's why I try to cleansing it, do you have any idea how to do it using TSQL



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
Post #1567382
Posted Sunday, May 4, 2014 7:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 35,769, Visits: 32,432
Eirikur Eiriksson (5/4/2014)
thava (5/3/2014)
hi,
hope everybody doing good,
here is my problem this is my data


The problem is in the data, each node can only have one parent!


For classic tree's, like ORG charts or BOM's, that might be true but there are "Nets" like roads between cities that can have multiple "parents" (not really parents but relations).


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1567384
Posted Sunday, May 4, 2014 7:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 35,769, Visits: 32,432
thava (5/4/2014)
Yes I know it, that's why I try to cleansing it, do you have any idea how to do it using TSQL


You haven't really identified what this "Net" is for. It may very well be that it was never meant to be a classic "Adjacency List" where each child ID is unique and the list is acyclic. It may be the totally wrong thing to do to "clean it".

What does this list of relational nodes actually represent?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1567385
Posted Sunday, May 4, 2014 8:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
Thank's jeff I got mad about this problem, I just want some hint from any one angle, that's why I posted like it, you are right it is a many to many relationship, Well,it may be a long story, in short, we are working on a trace matrix, where every document is related with multiple tables, and every table has mapped documents based on its position , we want to find the dependency of a documents based on the table, I am not able to post structure or the data right now, and I am feeling the table design is more normalized, now I am in out of my system, once I came in front of my system I will explain more clearly thanks again



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
Post #1567397
Posted Sunday, May 4, 2014 9:17 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 2,532, Visits: 7,069
thava (5/4/2014)
Yes I know it, that's why I try to cleansing it, do you have any idea how to do it using TSQL


I'm not certain that a recursive CTE is the right approach here.
Quick questions to start with:
How many edges/levels would you have from end to end?
Can a node/entry be an ancestor of itself?
Would Mainid == Referenceid denote a start/start/top level entry? (My guess is that there are many entry/start points).





Post #1567401
Posted Sunday, May 4, 2014 11:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
Mainid is not directly related with Referenceid but it is used to group the referenceid, if i give the parameter values as 2 i need the following result set
1	1
1 2
1 3
2 1
2 3
3 3
3 2
4 3
4 4
5 1
5 3
9 4
9 9
9 10
9 12

i will explain you now we achieve this, we need to find the groups where the value 2 lies so in our data
in mainid 1 and 3
i that group we have other reference id's so the result might be
1	1
1 2
1 3
3 3
3 2
[code]
now i have to do the same process again for the reference id 1 and 3 because these are siblings for the reference id 2
and the process is go on until i am not able find any records
os now for 1 the resultset will be
[code]
1 1
1 2
1 3
2 1
2 3

for 3 the result will be
1	1
1 2
1 3
2 1
2 3
3 3
3 2
4 3
4 4

because all the groups have the value 3
now form the result we have to find the result set for 4, it is
4	3
4 4
9 4
9 9
9 10
9 12

sin ce there is no result for the values 9 10 12 the loop ends here

if i give the parameter values as 5 or 6 or 7 then i want to return the following resultset
6	5
6 6
7 6
7 7
8 6
8 7

hope you got it to clarify me




Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
Post #1567410
Posted Sunday, May 4, 2014 11:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 35,769, Visits: 32,432
Sounds like the ol "Traveling Salesman" problem. It CAN be done using a recursive CTE (rCTE) with a "stop" in it. I'll see if I can find some old code for this but not tonight. It's 1:30AM and I've gotta take a nap before I get up for work tomorrow (today).


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1567411
Posted Sunday, May 4, 2014 11:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
thanks a lot i am waiting for it, take a deep sleep and have a good health we need you very much dear



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
Post #1567412
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse