Group By from 3 tables at once? 1

[nego78]

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

 

[ericbrunson]

What is "IDProduktu" ?

 

[r937]

you are getting a cross join effect

when you join one A row to multiple B rows, and then the same A row to multiple C rows, the number of rows in the intermediate result set, i.e. prior to grouping, will be B*C

this is what you get when you group by A and count(c.id)

you can try count(distinct c.id) instead -- there will still be B*C rows, but you'll only count one-Bth of them (if you know what i mean)

rudy

http://r937.com/

SQL Consulting

 

[nego78]

Hi

ericbrunson: sorry i was translating on the fly and i copied my names

IDProduktu == IDProduct

r937: thank you, once again you've proofed that you're expert

Webdesign