Complex Query

Question

Post reply

Complex Query

BeginnerBug

Hall of Fame

Points: 3539
More actions
March 9, 2011 at 1:38 am

#157479

create table #sample(sno int identity,student_no int, head int,task varchar(50))
insert into #sample (student_no,head,task) values(1,10,'tactical')
insert into #sample (student_no,head,task) values(10,20,'basket')
insert into #sample (student_no,head,task) values(20,40,'aerospace')
insert into #sample (student_no,head,task) values(40,10,'robot')
insert into #sample (student_no,head,task) values(10,40,'tackle')
insert into #sample (student_no,head,task) values(40,60,'trick')
if my input is student_no= 1
it shud show
student-no task
----------- -------
10 basket
20 aerospace
40 robot
10 tackle---> here is tricky thing
40 trick---->here too
using student-no input i have to get next head value and loop through it.
Anyway to acheive it?... any help pls...
i tried CTE..but it doesnt work as duplicated values occurs.... any suggestion pls?
thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 1

I'm not entirely sure I understand the requirement, but here goes..

You are using student_no is your 'pointer' and you wish to output values greater than the value specified.

You could use a stored procedure with a cursor (alter the order by clause to suit your own needs)

USE [MyDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_TestLoop]

@student_no AS Int

WITH EXECUTE AS OWNER

AS

BEGIN

--DECLARE @sno AS Int

DECLARE @head AS Int

DECLARE @task AS VARCHAR(50)

SET NOCOUNT ON;

DECLARE c1 CURSOR FAST_FORWARD FOR

SELECT student_no, head, task

FROM [MyTable]

WHERE student_no > @student_no

ORDER BY sno

OPEN c1 FETCH NEXT FROM c1 INTO @student_no, @head, @task

WHILE @@FETCH_STATUS = 0

BEGIN

--Load table or output HERE

PRINT CAST(@student_no AS VARCHAR(10)) + ', ' + @task

FETCH NEXT FROM c1 INTO @student_no, @head, @task

END

CLOSE c1

DEALLOCATE c1

SET NOCOUNT OFF;

END;

Obviously you need to pass the @student_no Parameter to the SP.

HTH.

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 2

Thanks Derby... but i cant use SP.. just CTE or select - query kind of....thats the problem... 🙁

Ken McKelvey SSCoach Points: 18856 More actions · Answer 3

BeginnerBug (3/9/2011)
create table #sample(sno int identity,student_no int, head int,task varchar(50)) insert into #sample (student_no,head,task) values(1,10,'tactical') insert into #sample (student_no,head,task) values(10,20,'basket') insert into #sample (student_no,head,task) values(20,40,'aerospace') insert into #sample (student_no,head,task) values(40,10,'robot') insert into #sample (student_no,head,task) values(10,40,'tackle') insert into #sample (student_no,head,task) values(40,60,'trick')
if my input is student_no= 1
it shud show
student-no task
----------- -------
10 basket
20 aerospace
40 robot
10 tackle---> here is tricky thing
40 trick---->here too
using student-no input i have to get next head value and loop through it.
Anyway to acheive it?... any help pls...
i tried CTE..but it doesnt work as duplicated values occurs.... any suggestion pls?
thanks in advance

I think you are going to have to explain how you arrive at your output.

(I hope you are not replying on the order you insert data into #sample as a table is an UNORDERED set.)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

BeginnerBug (3/9/2011)
create table #sample(sno int identity,student_no int, head int,task varchar(50)) insert into #sample (student_no,head,task) values(1,10,'tactical') insert into #sample (student_no,head,task) values(10,20,'basket') insert into #sample (student_no,head,task) values(20,40,'aerospace') insert into #sample (student_no,head,task) values(40,10,'robot') insert into #sample (student_no,head,task) values(10,40,'tackle') insert into #sample (student_no,head,task) values(40,60,'trick')
if my input is student_no= 1
it shud show
student-no task
----------- -------
10 basket
20 aerospace
40 robot
10 tackle---> here is tricky thing
40 trick---->here too
using student-no input i have to get next head value and loop through it.
Anyway to acheive it?... any help pls...
i tried CTE..but it doesnt work as duplicated values occurs.... any suggestion pls?
thanks in advance

It's tricky because the hierarchy is cyclic. A recursive CTE works fine if you restrict new rows to those which have a higher ID than the current row, like this:

;WITH rCTE AS (

SELECT sno, student_no, head, task, 0 AS [Level]

FROM #sample

WHERE student_no = 1

UNION ALL

SELECT c.sno, c.student_no, c.head, c.task, p.[level] + 1

FROM #sample c

INNER JOIN rCTE p ON p.head = c.student_no AND p.sno < c.sno

)

SELECT DISTINCT sno, student_no, head, task

FROM rCTE

WHERE [Level] BETWEEN 1 AND 4

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 5

thats works like a charm... thanks a lot chris 🙂

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 6

BeginnerBug (3/9/2011)
thats works like a charm... thanks a lot chris 🙂

For now it works like a charm. What's going to happen when you need to make a modification where you insert larger numbers up higher on the hierarchy?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 7

Hello Jeff... i implemented it in my coding which has more columns..it works fine.. is there any other way to make it simple?:-)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 8

BeginnerBug (3/9/2011)
Hello Jeff... i implemented it in my coding which has more columns..it works fine.. is there any other way to make it simple?:-)

Adding more columns isn't the problem I was concerned with. 😉 What I'm worried about is when you add a new Head or Student_ID which is out of the "natural order" of things.

Also, I'm not sure why Student_ID is listed more than once unless you have multiple parents for each student. Most folks try to run a "down only" type of "Directed Acyclic Graph" where each node has one and only one parent as you would find in an org chart rather than a geneology chart. You can still easily determine both downline and uplines using DAGs.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)