Recursive SQL JOIN without a Function or Cursor?

Question

I have a table which holds a list of criminal charges.These charges can be substituted.. for example, if a chap has been charged with assault but during the trial the victim dies, so the charge gets substituted to murder.

We have a Substitution table, which holds a From_Offence, and a To_Offence. When the charge is substituted, we create a new charge, and then in the substitution table, record the From ID, and then 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
)

A charge can be substituted many times. So, Charge 1 became Charge 2, but then later Charge 3. And then maybe charge 3 becomes charge 4.

You would have:

FROMID  TOID
1        2
2        3
3        4

The requirement is to return a list of charge IDs based on a current charge ID.

So, in english, the developer will pass me ChargeID:4, and I need to return the history of that charge (including it's self). And my result set would be:

I can maybe do a function, GetPreviousChargeId, and then somehow recursivly do something? But, I was hoping there might be a smart way to accomplish this.

Hopefully there is a way.

Take a look at this: http://stackoverflow.com/questions/6401222/connect-by-or-hierarchical-queries-in-rdbms-other-than-oracle — Glenn, Mar 22 '12 at 01:24
Ahh, there [are recursive CTEs in 2005](http://msdn.microsoft.com/en-us/library/ms186243%28v=sql.90%29.aspx). Have fun! — , Mar 22 '12 at 01:28

Justin Pihony · Accepted Answer · 2012-03-22T01:47:18.887

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;

Wow, Justin Pihony, that seems to work! I just have no idea how that logic works. Looking at it now. I am not understanding your comment as well. Should not be a SELECT 4? — Craig, Mar 22 '12 at 01:41
Thanks Justin. That's fantastic! Comments are really helpful too. I haven't used CTEs enough, which is bad, but this shows their power. Thanks. — Craig, Mar 22 '12 at 01:55

Recursive SQL JOIN without a Function or Cursor?

1 Answers1