query help - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2014-01-17 : 14:21:01

Hi All
Dont know whether this is something possible but want to bring it up to the experts for some advise

I have a table A where we store all the requests submitted by customers . Few requests are recurring every year . So we started a new process where we create a draft request and save in Table B for the requestors conveneince . this draft request will be an exact copy of the previous year request with a new requestid . we save the previous year requestid in a column called prevrequestid to track and when this happens for a period of time how do i track all the requests associated to the original requestid

Sample Data Requestid PrevRequestid
Request Submitted in 2009 - 123456 NULL
Request Submitted in 2010 - 123457 123456
Request Submitted in 2011 - 123458 123457
Request Submitted in 2012 - 123459 123458
Request Submitted in 2013 - 123460 123459
Request Submitted in 2013 - 123461 123460

when we create a draft requestid in Table B we take the current year request to create draft request for the next year.when the requestor submits the draft it gets into the Table A

My query should be able to pull all the requests so for example if i give select * from Table A where requestid is 123456 it should be able to identify all the connected requests and be able to pull everything

Is this something possible or what should i do to make it possible?

Thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-17 : 18:47:07

You could use a recursive CTE. BOL has details. I'd give some code but I don't have your schema.
HTH

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-18 : 04:39:04

something like this


DECLARE @RequestID int 
SET @RequestID = 123456 
;With CTE
AS
(
SELECT *
FROM Table 
WHERE RequestID = @RequestID
UNION ALL
SELECT t.*
FROM CTE c
INNER JOIN Table t
ON t.RequestID = c.PrevRequestID
)

SELECT *
FROM CTE
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2014-01-21 : 09:51:02

thanks Visakh . tested your code and ran into an issue

I submitted 4 test requests each using the previous request for example

first requestid is 149490
Second requestid is 149551 submitted using 149490
third requestid is 149564 submitted using 149551
fourth requestid is 149565 subitted using 149564

When i input 149565 in line 2 of your code which is the last request submitted i get all the previous requests but when i input 149490 or 149551 i dont get all of them

Hope i made sense

Thanks

quote:
Originally posted by visakh16

something like this

DECLARE @RequestID int
SET @RequestID = 123456
;With CTE
AS
(
SELECT *
FROM Table
WHERE RequestID = @RequestID
UNION ALL
SELECT t.*
FROM CTE c
INNER JOIN Table t
ON t.RequestID = c.PrevRequestID
)

SELECT *
FROM CTE
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 06:42:09

quote:
Originally posted by jim_jim

thanks Visakh . tested your code and ran into an issue

I submitted 4 test requests each using the previous request for example

first requestid is 149490
Second requestid is 149551 submitted using 149490
third requestid is 149564 submitted using 149551
fourth requestid is 149565 subitted using 149564

When i input 149565 in line 2 of your code which is the last request submitted i get all the previous requests but when i input 149490 or 149551 i dont get all of them

Hope i made sense

Thanks

quote:
Originally posted by visakh16

something like this

DECLARE @RequestID int
SET @RequestID = 123456
;With CTE
AS
(
SELECT *
FROM Table
WHERE RequestID = @RequestID
UNION ALL
SELECT t.*
FROM CTE c
INNER JOIN Table t
ON t.RequestID = c.PrevRequestID
)

SELECT *
FROM CTE
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

do you mean even if youstart from intermediate request you need to get all on both directtions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 06:43:56

something like this?


DECLARE @RequestID int 
SET @RequestID = 123456 
;With CTE
AS
(
SELECT *
FROM Table 
WHERE RequestID = @RequestID
UNION ALL
SELECT t.*
FROM CTE c
INNER JOIN Table t
ON t.RequestID = c.PrevRequestID
UNION ALL
SELECT t.*
FROM CTE c
INNER JOIN Table t
ON t.PrevRequestID = c.RequestID
WHERE NOT EXISTS (SELECT 1 FROM CTE WHERE RequestID = t.RequestID
                  AND PrevRequestID = t.PrevRequestID)
)

SELECT *
FROM CTE
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2014-01-22 : 09:14:30

Visakh
Get the below error message

Msg 253, Level 16, State 1, Line 3
Recursive member of a common table expression 'CTE' has multiple recursive references.

quote:
Originally posted by visakh16

something like this?

DECLARE @RequestID int
SET @RequestID = 123456
;With CTE
AS
(
SELECT *
FROM Table
WHERE RequestID = @RequestID
UNION ALL
SELECT t.*
FROM CTE c
INNER JOIN Table t
ON t.RequestID = c.PrevRequestID
UNION ALL
SELECT t.*
FROM CTE c
INNER JOIN Table t
ON t.PrevRequestID = c.RequestID
WHERE NOT EXISTS (SELECT 1 FROM CTE WHERE RequestID = t.RequestID
AND PrevRequestID = t.PrevRequestID)
)

SELECT *
FROM CTE
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 05:57:38

what about this?


DECLARE @RequestID int 
SET @RequestID = 123456 
;With CTE
AS
(
SELECT *
FROM Table 
WHERE RequestID = @RequestID
UNION ALL
SELECT t.*
FROM CTE c
INNER JOIN Table t
ON t.RequestID = c.PrevRequestID
OR t.PrevRequestID = c.RequestID
)

SELECT *
FROM CTE
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2014-01-23 : 08:16:00

The query runs endlessly and is returning the same records multiple times

Thanks

quote:
Originally posted by visakh16

what about this?

DECLARE @RequestID int
SET @RequestID = 123456
;With CTE
AS
(
SELECT *
FROM Table
WHERE RequestID = @RequestID
UNION ALL
SELECT t.*
FROM CTE c
INNER JOIN Table t
ON t.RequestID = c.PrevRequestID
OR t.PrevRequestID = c.RequestID
)

SELECT *
FROM CTE
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-24 : 00:55:40

ok here you go with logic to get both side info


declare @test table
(
Requestid int,
 PrevRequestid int
 )
 
insert @test
values(123456, NULL),
(123457, 123456),
(123458, 123457),
(123459, 123458),
(123460, 123459),
(123461, 123460)

declare @StartID int=123459

;With CTE
AS
(
SELECT *
FROM @test
WHERE Requestid = @StartID
UNION ALL
SELECT t.*
FROM CTE c
JOIN @test t
ON t.Requestid = c.PrevRequestid
),CTE1
AS
(
SELECT *
FROM @test
WHERE PrevRequestid = @StartID
UNION ALL
SELECT t.*
FROM CTE1 c
JOIN @test t
ON t.PrevRequestid = c.Requestid
)

SELECT *
FROM CTE
UNION ALL
SELECT *
FROM CTE1
OPTION (MAXRECURSION 0)

Requestid	PrevRequestid
123459	123458
123458	123457
123457	123456
123456	NULL
123460	123459
123461	123460

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2014-01-24 : 08:32:09

Thanks so much Visakh . It worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources