I have inherited a db, and I need to transform an existing parent-child relationship into all parents.

Let me first explain the existing data.The tables have a structure something like:

Thing                  Widget ------------           ------------ thing_id               widget_id thing_name             widget_name thing_rank             widget_rank                        thing_id 

The data is presented to the end user as a list of Thingss, and occasionally a Thing has-a Widget, and the widget is presented as being “beneath” or “sub” to the Thing. So there’s some hierarchy. The other key thing is that this presentation is sorted by the thing_rank and widget_rank.

Here’s a concrete example with data:

Things:

1001  "thing blue" 1 1002  "thing green" 3 1003  "thing red" 4 1004  "thing yellow" 2 

Widgets:

2001  "widget cucumber" 2  1002 2002  "widget bean" 1  1002 

This would be presented with the Things ordered by rank, then the two child Widgets belong to “thing green”, and ordered by their rank:

1 thing blue 2 thing yellow 3 thing green    1 widget bean    2 widget cucumber 4 thing red 

The task is to remove the hierarchy, but preserve the rank order. For example, the above data would be assigned a new rank, and all data would belong to the same relation — no longer a parent-child relationship. For example:

1 thing blue 2 thing yellow 3 thing green 4 widget bean 5 widget cucumber 6 thing red 

And the single relation would be:

Stuff ------------- stuff_id stuff_type   (thing|widget) stuff_name stuff_rank 

I’m looking for how to create a Stuff view that presents both Thing and Widget data, with new rank assignments.