Recursive Function appropriate?

Question

Hi guys wondering could yous help me with a recursive query within SQL. Or even if a recursive query is the right choice. I have columns like so lets say

ID | CUS | CASHIERID | RECEIPTID | PAYMENTNUM | ORIGINALRECEIPT

Now assume there is data like so:

+----------+--------+-------------+-------------+--------------+------------------+
|     ID   |  CUS   |  CASHIERID  |  RECEIPTID  |  PAYMENTNUM  |  ORIGINALRECEIPT |
+----------+--------+-------------+-------------+--------------+------------------+
|       1  |  jeff  |           2 |        123  |       00005  |  NULL            |
|       4  |  jeff  |           2 |        548  |       00005  |  123             |
|      16  |  jeff  |           2 |        897  |       00005  |  123             |
|     151  |  jeff  |           2 |       1095  |       00005  |  123             |
+----------+--------+-------------+-------------+--------------+------------------+

Now say the Database was Huge and there could be X amount of related receipts as we see above ID is the original and the all others are related (refunds or something). Now say I was given the RECEIPTID for any one of these. To get all parent/child rows of this what is the best route? My first initial thought is to just simply do a sort of IF ELSE lets say and if ORIGINALRECEIPT is not empty then do a where clause with whatever is in it. But for sake of argument would you be able to do a recursive query of sorts to be able put in any receiptID and receive all 4 records back

EDIT

Hi guys so bit of a change so I got a recursive function working but now you see the data base is HUGE and when I perform the recursive function which is finding all reissued receipts (new ones) after the user inputs a receipt ID so user inputs receiptID, this then runs a recursive query that gets all related newer receipts by using the 'prevRecep' column which has the before receiptID in it so like a chain as mentioned in the comments. I have it working no problem on the small test database but the HUGE DB is super slow its been 40 mins and still has not finished. there is an index on CU,cashierid,receiptid but unfortnately for now I can't have an index on any other column. So I know that will already really slow my query down as im using the prevRecep column in it but is there any way I can quicken it up or better approach? Below is the recursive query

with cte as (
  select *
  from receipts
  where cus='jeff' and casherid='2' and receiptid= '548'

  union all

  select cur.*
  from receiptscur, cte
  where cur.prevRecep = cte.recieptID
)
select * from cte

Can there be a chain, e.g. receipt 987 / original 654 => receipt 654 / original 321 => receipt 321 / original null? — Thorsten Kettner
– Thorsten Kettner, Commented May 7, 2020 at 15:43
@ThorstenKettner Yes sorry I actually forgot to include a column but yes basically its a column called 'prevRecep' so for the above example going downwards it would go: 123 / null => 548 / 123 => 897 / 548 etc.. — K.Madden
– K.Madden, Commented May 8, 2020 at 8:14
No, I don't think so. The query is fine, so it's database measures (indexes, memory, partitions and the like) or hardware that's needed to get it faster. — Thorsten Kettner
– Thorsten Kettner, Commented May 8, 2020 at 11:38
On a sidenote (this doesn't have anything to do we speed): Don't use comma separated-joins, even in such small queries. We've had explicit joins (INNER JOIN ... ON) for almost thirty years now. It's time to say good-bye to the 1980's style joins. And when dealing with numeric columns don't use strings that must be converted to numbers. I expect casherid and receiptid to be numeric, hence: where cus = 'jeff' and casherid = 2 and receiptid = 548. — Thorsten Kettner
– Thorsten Kettner, Commented May 8, 2020 at 11:45

Marc Guillot · Accepted Answer · 2020-05-07 16:23:13Z

2

Yes, a recursive query should be fine :

declare @ReceiptId int = 123;

with cte as (
  --These are the anchor (the parents)
  select *
  from Receipts
  where ReceiptId = @ReceiptId and OriginalReceipt is null  

  union all

  --These are the recursive childs. Could be multiple levels : parent, child, subchild, ...
  select Receipts.*
  from Receipts
       inner join cte on cte.ReceiptId = Receipts.OriginalReceipt
)
select * from cte;

By the way, if your parent-child relations don't have more than one level, then the query doesn't need to be recursive, a simple UNION would be enough:

declare @ReceiptId int = 123;

select *
from Receipts
where ReceiptId = @ReceiptId 

union all

select Receipts.*
from Receipts
where OriginalReceipt = @ReceiptId

edited May 7, 2020 at 16:23

answered May 7, 2020 at 15:46

Marc Guillot

6,5431 gold badge23 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

K.Madden Over a year ago

Amazing it worked a charm and helped me a lot. Could you see the qs new EDIT?

Marc Guillot Over a year ago

@K.Madden you already know that the answer is to add an index for prevRecep, why don't you open a new question and tell us what problem do you encounter when trying to create that index ?, maybe we can help you to create it.

Collectives™ on Stack Overflow

Recursive Function appropriate?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related