2

I'm have a MySQL 5.1 DB with this table (paraphrasing)...

contracts
---------
id
parent_id
end_date

So contracts can have parent-child relationships via parent_id, and there can be several levels of nesting.

Given some contract with an id of let's say 1, how can I write a query to find the last end_date among it and its descendants?

(It would also be great to get the ids of the contract(s) with that end_date.)

Ethan
  • 57,819
  • 63
  • 187
  • 237
  • 1
    You can't, as far as I know you need multiple queries. – Dany Caissy Jun 21 '13 at 01:13
  • 1
    MySQL doesn't support recursive CTE's but here is a solution using a stored procedure: [Psuedo CTE](http://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159) – Hart CO Jun 21 '13 at 01:16
  • Ah, OK. I'll use some sort of caching column or table then, along the lines KeepCalmAndCarryOn suggested. – Ethan Jun 21 '13 at 01:54

1 Answers1

1

Recursion in SQL can be difficult to implement - what happens if you get a circular relationship too?

I would consider breaking the relationships out into a separate table which explicitly lists them all.

eg 

 contact_id     super_id     relationship
 1              10           Parent
 1              20           Grandparent
 10             20           Parent

and so on. Its a bit of work to keep them up to date but worth the effort. Its also a simple matter to join a contract to all its parents

KeepCalmAndCarryOn
  • 8,817
  • 2
  • 32
  • 47