Question :
In MS SQL server, I have a table A as below
Name Food PreferenceOrder
Jon Burger 1
Jon Coke 2
Jon Pizza 3
Jon Fries 4
Sam Pizza 1
Sam Coke 2
I have another table B that can override the preference order above
Name Food PreferredOverFood
Jon Pizza Fries
Jon Coke Burger
Jon Fries Coke
Sam Coke Pizza
Basically here, Food should come before PreferredOverFood (Pizza > Fries)
Now, I want to reorder table A according to Table B preferences, so the result should be like
Name Food PreferenceOrder
Jon Burger 4
Jon Pizza 1
Jon Fries 3
Jon Coke 2
Sam Pizza 2
Sam Coke 1
I tried by using cursors, so I created a dynamic cursor, with each fetch I am updating Table B with table A preference, but since we are updating things row by row its not considering rows that violate the previous preferences, so I am getting Fries before Pizza (since Fries > Coke is run and it forgot about first preference (Pizza > Fries)).
So dynamic cursor is not working, (its not refreshing the result set after update). Can I use CTE or something to do like above. (Can also have circular dependencies, but not too worried about it for now)
Thanks
Answer :
It is too long for a comment.
Well, that’s the problem, then. I take data from A
, then apply overrides from B
to it and end up with a cycle. I think that there is a cycle, you don’t think that there should be a cycle.
So, there is a bigger problem with understanding how overrides should work.
My logic is the following.
Data from A
can be represented in the same format as B
:
Name Food PreferenceOrder
Jon Burger 1
Jon Coke 2
Jon Pizza 3
Jon Fries 4
So, for Jon
we have this chain originally:
Burger -> Coke -> Pizza -> Fries
OR this set of rules:
a) Burger -> Coke
b) Coke -> Pizza
c) Pizza -> Fries
Then we have overrides from B
:
Name Food PreferredOverFood
Jon Pizza Fries
Jon Coke Burger
Jon Fries Coke
OR this set of rules:
d) Pizza -> Fries
e) Coke -> Burger
f) Fries -> Coke
Now we merge two sets of rules where second set overrides the first set if there is a conflict.
Rule (d) is the same as (c), so (c) is removed.
Rule (e) overrides (a), so (a) is removed.
Rule (f) is a new rule that doesn’t exist in the first set.
If any rules from the first set are still there, they are kept.
Final result of rules:
d) Pizza -> Fries
e) Coke -> Burger
f) Fries -> Coke
b) Coke -> Pizza
This is a directional graph. If it represents a tree, it can be ordered. It there are cycles, you need to decide what to do with them. If there are several disconnected trees, you can order each tree individually and then decide the order between trees.
In your example the graph looks like this (if we start from Coke).
(b) (d) (f)
Coke -> Pizza -> Fries -> Coke -> ... the cycle (bdf) ...
|
| (e)
|
-> Burger