Recursive SQL JOIN without function or cursor?
I have a table that contains a list of criminal charges. These charges can be changed. For example, if the head is accused of assault, but during the trial the victim dies, so the charge is changed to murder.
We have a lookup table that contains the From_Offence and To_Offence values. When the charge is substituted, we create a new charge, and then write the From identifier in the substitution table, and then the new To_Id.
CREATE TABLE [dbo].[ijis_court_item_association](
[ijis_court_item_association_id] [int] IDENTITY(1,1) NOT NULL,
[from_ijis_court_item_id] [int] NOT NULL,
[to_ijis_court_item_id] [int] NOT NULL
)
The charge can be changed many times. So Charge 1 became Part 2, but then Charge 3. And then maybe Charge 3 becomes Charge 4.
You will have:
FROMID TOID
1 2
2 3
3 4
The requirement is to return a list of Charge IDs based on the current Charge ID.
So, in English, the developer will give me ChargeID: 4, and I need to return the history of this charge (including himself). And my result set:
4 3 2 1
Perhaps I can make the GetPreviousChargeId function, and then somehow recursively do something? But I was hoping there might be a sane way to achieve this.
I hope there is a way.
source to share
I believe this should work. As mentioned, this is a Recursive CTE
WITH Charges AS
(
--This should not be just a SELECT 4 because if no matches are found
--then it will return a null and not recurse at all
--This query will only run once at the beginning
--(it is the anchor to the recursion)
SELECT to_ijis_court_item_id AS CourtID
FROM ijis_court_item_association
WHERE to_ijis_court_item_id = 4
UNION ALL
--This is the actual recursion, continuing to query until no results are found
--It uses the first queries data to begin
SELECT from_ijis_court_item_id AS CourtID
FROM ijis_court_item_association
JOIN Charges
ON Charges.CourtID = ijis_court_item_association.to_ijis_court_item_id
)
--This is the final output from all of the above queries (however many there are)
--union all'ed together
SELECT * FROM Charges;
source to share