0

For my product table I have the following (for this question) important columns: 

{{id}} - primary
{{name}} - product name
{{successor}} - containing the previous id

For historization reasons I insert new products and link them via the successor column instead of updating or deleting the line (my active products are defined by "successor is null"), so I might end up with content like this: 

1 - Dummy #1 - 3
2 - Dummy #2 - null
3 - Dummy #1 Updated - 4
4 - Dummy #1 Updated again - null

For a report I'd like to select all orders containing some of these products but I'd need to select orders too which contain a previously updated product.

So what I'm looking for is a way to (probably directly in MySQL) select lines 1 and 3 though my where clause looks like " ... where id = 4" but for what I was able to research this seems not possible.

So the simple question would be: is it possible or not? and if yes, what's the best approach to this?

kind regards

gmaltasteffl
  • 25
  • 1
  • 6
  • 1
    It is possible. How complicated it is will depend on your MySQL version, as MySQL 8 supports recursive ctes, which will simplify this task a lot. Have a look [here](https://stackoverflow.com/q/20215744), it contains a pretty complete list of options, including solutions for MySQL 8 and ideas where you would modify your data structure to be more "database friendly". In case you do not actually need the history/hierarchy, you can also store your items like duplicates: `(id, active_id)`, e.g. `(1,4), (2,2), (3,4), (4,4)`, then you do not need a hierarchical query. – Solarflare Dec 22 '18 at 09:33
  • @Solarflare: Thanks very much, this seems to be exactly what I'm looking for ... must have slipped my fingers when searching – gmaltasteffl Dec 22 '18 at 11:32

0 Answers0