CODEWITHSUNDEEP
sqlsql-server
K.Madden
K.Madden

Reputation: 210

Recursive Function appropriate?

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

Upvotes: 0

Views: 57

Answers (1)

Marc Guillot
Marc Guillot

Reputation: 6455

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

Upvotes: 2

Related Questions