countrymeister
asked on
Query syntax on conditional column joins
I have two tables ProductRatio and Products, here is some sample data
ProductRatio
Category Group ProductID Ratio
NULL NULL NULL 0.12
Alpha 0.15
Alpha Group1 0.22
Alpha Group1 123 0.27
Products
Category Group ProductID Ratio
Alpha Group1 123
Alpha Group2
Generic Group1
I need to update the Products table Ratio column such that
If there is an exact match between Category, Group, and ProductID from the ProductRatio table then use that value.
If one does not exist use the Category and Group value.
If that does not exist just use the Category value,
If that is not there than default to the NULL value ,which in this case is 0.12
Based on this hierachy the values updated for ratio column in the Products table would be
Products
Category Group ProductID Ratio
Alpha Group1 123 0.27
Alpha Group2 0.15
Generic Group1 0.12
ProductRatio
Category Group ProductID Ratio
NULL NULL NULL 0.12
Alpha 0.15
Alpha Group1 0.22
Alpha Group1 123 0.27
Products
Category Group ProductID Ratio
Alpha Group1 123
Alpha Group2
Generic Group1
I need to update the Products table Ratio column such that
If there is an exact match between Category, Group, and ProductID from the ProductRatio table then use that value.
If one does not exist use the Category and Group value.
If that does not exist just use the Category value,
If that is not there than default to the NULL value ,which in this case is 0.12
Based on this hierachy the values updated for ratio column in the Products table would be
Products
Category Group ProductID Ratio
Alpha Group1 123 0.27
Alpha Group2 0.15
Generic Group1 0.12
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.