Hi i've got a problem with proper counting.
There are 3 tables.
Products
IDProduct,Name
ProductPrices
IDProductPrice,IDProduct,DateOfChange,Price
ProductsImages
ID,IDProduct,Image
i'd like to select all products from Products table left joined with proper price and counted number of images.
i do it in that way:
SELECT
a.IDProduct
,a.Name
,SUBSTRING_INDEX(MAX(CONCAT(b.DateOfChange,'|',b.Price)), '|', -1) AS Price
,COUNT(c.ID)
FROM Products AS a
LEFT JOIN ProductPrices AS b ON b.IDProduktu=a.ID
LEFT JOIN ProductImages AS c ON b.IDProduktu=a.ID
GROUP BY a.ID
Why in that way? Because i need to keep a history of price changes. So every time i change price i insert it into table with products id and date of change
Everything is ok when quantity of prices for one products equals 1. In other situation Count(c.ID) show multiply value of Count(c.ID) and quantity of prices for that Products
Any ideas how to solve it?
Tx in advance
Webdesign
There are 3 tables.
Products
IDProduct,Name
ProductPrices
IDProductPrice,IDProduct,DateOfChange,Price
ProductsImages
ID,IDProduct,Image
i'd like to select all products from Products table left joined with proper price and counted number of images.
i do it in that way:
SELECT
a.IDProduct
,a.Name
,SUBSTRING_INDEX(MAX(CONCAT(b.DateOfChange,'|',b.Price)), '|', -1) AS Price
,COUNT(c.ID)
FROM Products AS a
LEFT JOIN ProductPrices AS b ON b.IDProduktu=a.ID
LEFT JOIN ProductImages AS c ON b.IDProduktu=a.ID
GROUP BY a.ID
Why in that way? Because i need to keep a history of price changes. So every time i change price i insert it into table with products id and date of change
Everything is ok when quantity of prices for one products equals 1. In other situation Count(c.ID) show multiply value of Count(c.ID) and quantity of prices for that Products
Any ideas how to solve it?
Tx in advance
Webdesign