cte recursive query with a specific starting point and internal joins

Question

Lets say I have the following schema:

person (person_id, person_account, name)
relationships (person_id, father_id)
purchases(person_account, total, type)

Disclaimer: I did not design this schema, I know it's terrible so I apologize. Also this is an example, this is much more complicated. Also note that this is a query that I will use in JDBCTemplate, which is why I included the tag.

Now I want to get a count of a person's purchases, and his or her sons purchases and their sons purchases and so on. I want a specific starting point on the initial person.

Couple of things that differ from the examples I've seen so far:

1. A specific starting point (let's say it's person_account).
2. the table purchases has no person_id field, so it complicates things.
3. I want a count and not other stuff.
4. I want to filter by type of purchase.
5. Since the father_id relationship is in another table, it would mean a join has to be made. Again it's horrible but I cannot change the database to include a father_id field in the person table.
6. If a person has no father, there is NO entry in the relationships table. That will complicate it even further.

Now I've read the examples and I understand a couple of them:
How to get all children of a parent and then their children using recursion in query
SQL Server recursive query
http://msdn.microsoft.com/en-us/library/ms186243.aspx

However, I have problems understanding the starting and ending points. My starting point COULD have a father, so that cannot be null. My endpoints should get purchases from people that have no entries on the relationships table basically.

So what I'm thinking is:

declare @id int = 234 

;with CTEexample as (
     select p.person_account, p.person_id, p.type, r.father_id from purchases as s
         join person p on p.person_account = s.person_account
         join relationships r on r.person_id = p.person_id
         where r.person_id = @id
     union all
     select p.person_account, p.person_id, p.type, r_father_id from purchases as s
         join person p on p.person_account = s.person_account
         join relationships r on r.person_id = p.person_id
         join CTEexample c on p.person_id = r.father_id
 )
 select count(*) from CTEexample
      where type = 's'

However, it isn't working at all.

Any help would be appreciated.

Answer 1

You have a very detailed question but my guess is your predicate in the recursive CTE is causing havoc. Very often you need to do 2 important things with recursion and hunting down a tree:

1. You need to know what level you are on 'Position' (pos in my example)
2. You need to determine relationships from positions in your recursion to make the recursion meaningful. Else you are showing the same values again WITHOUT really using the recursive power of what it can do.

Here is an example that may apply to yours that:

1. Will give you a hierarchy
2. will find the farthest rung of a tree (the lowest position)
3. Add orders of the descendents and the child together.

The great benefit of recursion is not one or two levels but when you go 5 or more levels and having the power to tell the expression which part of it you want. I do 2 cte's usually when I do recursions, one to do the recursion and a second one to find the max recursion and then show me that. Otherwise every person is going to return with every level of heirarchy. Unless you really want that, you should limit it with a windowed expression.

I hope this helps, sometimes you have to tailor Recursive CTEs for your own situations for a while:

Declare @table table ( PersonId int identity, PersonName varchar(512), Account int, ParentId int, Orders int);

insert into @Table values ('Brett', 1, NULL, 1000),('John', 1, 1, 100),('James', 1, 1, 200),('Beth', 1, 2, 300),('John2', 2, 4, 400);

select 
    PersonID
,   PersonName
,   Account
,   ParentID
from @Table

; with recursion as 
    (
    select 
        t1.PersonID
    ,   t1.PersonName
    ,   t1.Account
    --, t1.ParentID
    ,   cast(isnull(t2.PersonName, '')
            + Case when t2.PersonName is not null then '\' + t1.PersonName else t1.PersonName end
            as varchar(255)) as fullheirarchy
    ,   1 as pos
    ,   cast(t1.orders + 
            isnull(t2.orders,0) -- if the parent has no orders than zero
            as int) as Orders
    from @Table t1
        left join @Table t2 on t1.ParentId = t2.PersonId
    union all
    select 
        t.PersonID
    ,   t.PersonName
    ,   t.Account
    --, t.ParentID
    ,   cast(r.fullheirarchy + '\' + t.PersonName as varchar(255))
    ,   pos + 1  -- increases
    ,   r.orders + t.orders
    from @Table t
        join recursion r on t.ParentId = r.PersonId
    )
, b as 
    (
    select *, max(pos) over(partition by PersonID) as maxrec  -- I find the maximum occurrence of position by person
    from recursion
    )
select *
from b
where pos = maxrec  -- finds the furthest down tree
-- and Account = 2  -- I could find just someone from a different department

Answer 2

Keep the recursion simple (easier for others to manage down the road) and use it to grab the relationships. From there, you can join to Person to get the account number and then the Purchases.

DECLARE @PersonID INT = 1

;WITH Family (PersonID, FatherID) AS (
  SELECT p.PersonID, null
  FROM Person p
  WHERE p.PersonID = @PersonID

  UNION ALL

  SELECT p.PersonID, r.FatherID
  FROM Person p
  INNER JOIN Relationships r
    ON r.PersonID = p.PersonID -- Me
  INNER JOIN Family f
    ON f.PersonID = r.FatherID -- Father
)
SELECT *
FROM Family f
JOIN Person p
  ON p.PersonID = f.PersonID
JOIN Purchases ps
  ON ps.PersonAccount = p.PersonAccount
WHERE ps.Type is null

SQLFiddle