SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


problem in logic for finding a Recursive Cte


problem in logic for finding a Recursive Cte

Author
Message
thava
thava
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 557
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
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39173 Visits: 19434
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!
Cool
thava
thava
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 557
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207342 Visits: 41961
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!
Cool


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207342 Visits: 41961
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
thava
thava
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 557
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
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39173 Visits: 19434
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).
Cool
thava
thava
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 557
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207342 Visits: 41961
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
thava
thava
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 557
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search