CODEWITHSUNDEEP
sqlsql-server-2005selectrecursive-query
Craig
Craig

Reputation: 18694

Recursive SQL JOIN without a Function or Cursor?

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:

4
3
2
1

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.

Upvotes: 3

Views: 685

Answers (1)

Justin Pihony
Justin Pihony

Reputation: 67075

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;

Upvotes: 2

Related Questions