I have a couple of tables, Orders and Products. The Orders table has ProductID and Quantity. The Products table has ProductID, Price, QuantityPrice, SalesPrice, and SalesQuantityPrice.
What I want to do is JOIN the two tables, and have the lowest price returned. The QuantityPrice and SalesQuantityPrice come into play if the Orders.Quantity > 1, otherwise Price or SalesPrice should be returned.
QuantityPrice, SalesPrice, and SalesQuantityPrice won't always exist. Sometimes the QuantityPrice might be lower than the SalesPrice.
Can this be done in a single SELECT statement? Could someone show me how, or at least point me in the direction of the syntax needed?
Any help greatly appreciated.
JRL
What I want to do is JOIN the two tables, and have the lowest price returned. The QuantityPrice and SalesQuantityPrice come into play if the Orders.Quantity > 1, otherwise Price or SalesPrice should be returned.
QuantityPrice, SalesPrice, and SalesQuantityPrice won't always exist. Sometimes the QuantityPrice might be lower than the SalesPrice.
Can this be done in a single SELECT statement? Could someone show me how, or at least point me in the direction of the syntax needed?
Any help greatly appreciated.
JRL