Query to combine multiple queries with Max and Min

[jtgurkin]

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...

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...

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.

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

 

[lameid]

Swap the order of the columns in your where clause...

WHERE Items.ItemID=PricesByScheme.ItemID

Becomes

WHERE PricesByScheme.ItemID = Items.ItemID


The first and your initial is more of a ANSI-89 Join and the latter actually limits the values returned by the subquery.

At least I think thats what is wrong.

 

[jbenson001]

On an inner join, the where clause column order does not matter.

 

[lameid]

My point is it is NOT an inner join he is interested in. It is a subquery with limited results.

 

[r937]

you said that this code --

SELECT Prices.*
FROM TopPricingSchemeByItem INNER JOIN Prices ON (TopPricingSchemeByItem.ItemID = Prices.ItemID) AND (TopPricingSchemeByItem.TopSchemeID = Prices.SchemeID);

selects the prices based on the top ranked selected pricing scheme

actually, all it selects is Prices.*

mind you, if there are some prices that don't have a matching row based on itemID and schemeID, those prices will be dropped, but otherwise you are still getting Prices rows in the result set


r937.com | rudy.ca

 

[jtgurkin]

actually it only selects prices.* for those prices that match the top pricing scheme

 

[lameid]

Have you tried the below? I now see that it wasn't obvious which select I was talking about.

SELECT Items.*,
(SELECT Max(PricesByScheme.Price) From PricesByScheme WHERE PricesByScheme.ItemID = Items.ItemID) AS MaxPrice,
(SELECT Min(PricesByScheme.Price) From Prices WHERE PricesByScheme.ItemID = Items.ItemID) AS MinPrice
FROM Items;

 

[jtgurkin]

yes, i have tried putting them in both orders. I'm creating this in access and when i try to run the query it pops up an input box asking for the two constraints.

 

[lameid]

The input box should be specifying which things it thinks is a parameter. In other words, it does not think it is a field and is asking for the value. If it is a field, try putting square brackets [] around each the table name and the field name...

I.e.
[Table Name].[Field Name]