I'm writing an ecommerce site and had the page display the price range for a particular item based on a prices table linked to the items table. I used the following query to get that information...
I was then told that I needed to allow for multiple pricing schemes based on time of year, stock supply, etc. so I build the following query...
This code selects the prices based on the top ranked selected pricing scheme.
Then i redesigned the initial query to pull from this one rather than directly from the prices table.
Now the query returns the max price in the database for every record and the same goes for the min price.
Please Help me fix this
Code:
SELECT Items.*,
(SELECT Max(Prices.Price) From Prices WHERE Items.ItemID=Prices.ItemID) AS MaxPrice,
(SELECT Min(Prices.Price) From Prices WHERE Items.ItemID=Prices.ItemID) AS MinPrice
FROM Items;
I was then told that I needed to allow for multiple pricing schemes based on time of year, stock supply, etc. so I build the following query...
Code:
SELECT Prices.*
FROM TopPricingSchemeByItem INNER JOIN Prices ON (TopPricingSchemeByItem.ItemID = Prices.ItemID) AND (TopPricingSchemeByItem.TopSchemeID = Prices.SchemeID);
This code selects the prices based on the top ranked selected pricing scheme.
Then i redesigned the initial query to pull from this one rather than directly from the prices table.
Code:
SELECT Items.*,
(SELECT Max(PricesByScheme.Price) From PricesByScheme WHERE Items.ItemID=PricesByScheme.ItemID) AS MaxPrice,
(SELECT Min(PricesByScheme.Price) From Prices WHERE Items.ItemID=PricesByScheme.ItemID) AS MinPrice
FROM Items;
Now the query returns the max price in the database for every record and the same goes for the min price.
Please Help me fix this