Can this be done with single SQL query
- by Ghostrider
I'm using MySQL. I have a table
Type SubType
1 1
1 5
1 6
1 8
2 2
2 3
3 1
3 2
3 3
For each type there is some number of subtypes. For every subtype in a type there is a corresponding subtype in the next type:
(1,1) => (2,2)
(1,5) => (2,3)
(1,6) => (2,2)
(1,8) => (2,3)
(2,2) => (3,1)
(2,3) => (3,2)
In case you haven't seen the pattern, here it is: you sort both current and next types by subtype, then in the next type you get subtype in the same position as your current subtype in current type is. If there are more subtypes in the current type that in the next one, you warp around and start from the first subtype in the next type.
Is it possible to construct a query that takes current type and subtype and returns corresponding subtype in the next type?
