pull values recursively from sql table

Question

create table #customer (
id int not null primary key,
cust_name varchar(12),
oldid int null
)

insert into #customer values(1,'XYZ',null) 
insert into #customer values(2,'XYZ',1) 
insert into #customer values(3,'XYZ',2) 
insert into #customer values(4,'ABC',null) 
insert into #customer values(5,'ABC',4) 
insert into #customer values(6,'DEF',null) 
insert into #customer values(7,'DEF',6) 
insert into #customer values(8,'DEF',7) 
insert into #customer values(9,'DEF',8) 


select * from #customer
-- output

id          cust_name    oldid
----------- ------------ -----------
1           XYZ          NULL
2           XYZ          1
3           XYZ          2
4           ABC          NULL
5           ABC          4
6           DEF          NULL
7           DEF          6
8           DEF          7
9           DEF          8

This is a simulation when a record is renewed, the new record stores the id of its older record. The chain continues and goes down to the very first record created for that customer.

What I want is I issue a command like

select * from #customer where id=3

This should put not only the record with id=3 but all its old version, that is records 2 and 1 also.

select * from #customer id=4

should pull only that record (oldid = null)

Enhancement (optional): If someone issue a commond

select * from #customer where id=8

I would somehow like to indicate, a new record exist for this customer. How can I do that? Assume I am going to use in ASP.NET application.

have a look at [this](http://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server) — StevieG, Feb 06 '12 at 19:50
I tried it myself for a couple of hours. I know you have to use `Common Table Expression` but can't get it right so far. — TheTechGuy, Feb 06 '12 at 19:53

score 1 · Answer 1 · answered Feb 06 '12 at 19:55

1

soething like this should work.. (Don't have access to a SQLServer session at the moment to test..)

WITH n(id, cust_name) AS 
   (SELECT id, cust_name 
    FROM customer
    WHERE id = @id
        UNION ALL
    SELECT nplus1.id, nplus1.cust_name 
    FROM emp as nplus1, n
    WHERE n.empid = nplus1.oldid)
SELECT * FROM n

answered Feb 06 '12 at 19:55

StevieG

8,639
23
31

I have made it work. It pulls the records in the opposite direction as of select answer. I may use it for something too. – TheTechGuy Feb 06 '12 at 20:10
Can you please explain `FROM #customer as nplus1, n WHERE n.id = nplus1.oldid`. What does n point to? nplus1 point to the #customer table which I know. – TheTechGuy Feb 24 '12 at 14:44
I think I got it but this is new to me. n refers the whole query as `with n(...)` still I would like to know about this syntax – TheTechGuy Feb 24 '12 at 14:50

score 1 · Accepted Answer · answered Feb 06 '12 at 19:56

1

Can get all of the older records for a given Customer using a CTE. Something like

DECLARE @CustomerId int
SET @CustomerId = 8

;
WITH Records (Id, Cust_name, OldId) AS (
    SELECT * FROM #Customer
    WHERE Id = @CustomerId
    UNION ALL
    SELECT C.* FROM Records R
        INNER JOIN #Customer C on C.Id = R.OldId)
SELECT * FROM Records

You have options for indicating whether a newer record exists, you may want to include newer record(s) in the result set or just a bit flag, but in any case the query would be like

SELECT * FROM #Customer WHERE OldId = @CustomerId

answered Feb 06 '12 at 19:56

Bort

7,398
3
33
48

I have +1'd this as well. Can you give me a hint, how to include the later record also in the sub query, if possible. – TheTechGuy Feb 06 '12 at 20:08
1

Add a UNION ALL between the two queries I have, that will return all the old records plus a new one, if it exists. – Bort Feb 06 '12 at 20:14

pull values recursively from sql table

2 Answers2