Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Group By from 3 tables at once? 1

Status
Not open for further replies.

nego78

Programmer
Jun 13, 2004
129
PL
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
 
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
SQL Consulting
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top