getting funky numbers from sum() 1

[11sven]

SELECT `itemProdId` , `itemProdQty` , `itemProdSize` , sizeUnitPrice, sum( `itemProdPrice` ) as calcTotal
FROM items
LEFT JOIN sizes ON itemProdId = sizeProductId
where `itemCustId` = -1
and `itemOrdId` = 36
group by itemProdId
i entered test data and this query only finds one row calcTotal should = 1.00 but when i run query calcTotal = 4.00 the only time i get the correct sum() is when i change it to a single table query?

 

[TonyGroves]

If you run the query without the SUM and GROUP BY bits, do you get the records you expect? And when you sum those records manually, do you get the result expected?

 

[11sven]

no, when I run this query,
SELECT itemProdId, itemCustId, itemProdQty, itemProdSize, sizeUnitPrice, itemOrdId
FROM items, sizes
WHERE itemOrdId =36
AND itemCustId = -1
AND itemProdId = sizeProductId
i get 4 rows with itemOrdId=36, when i browse my items table in phpmyadmin there is only one row with itemOrdId=36
i don't what you mean by summing them manually but i created this record in the data base so only one record should match the query, so yes when i sum manually i get 1.00 like i expect

 

[TonyGroves]

If there are 4 records in the Sizes table that match the Items record, then the join will produce 4 records; that's what joins are for. If you don't need information from the Sizes table, then don't join it.

 

[11sven]

that is the problem i need to display the sizeUnitPrice of a specific itemProdId from the sizes table.(item.itemProdId = size.sizeProductId) i think i see what is going on here when i state "WHERE itemOrdId =36
AND itemCustId = -1
AND itemProdId = sizeProductId"
i thoughht i was narrowing it down enough to say 'get the size ProductId from the sizes table where the itemProdId in the items table is from order 36 and customer -1' is this not correct? maybe it will be easier to run 2 seperate querys
or how reliable are sub-querys in php?
any way thanks for your help your questions changed the way i was thinking about the problem

 

[TonyGroves]

I'm afraid I don't know what you're trying to achieve. If you're still stuck, you could post your table structures (SQL query: "SHOW CREATE TABLE tablename"), and table contents ("SELECT * FROM tablename"), and tell us what results you're looking for.

PHP knows nothing about subqueries; all it does is pass your query text to the MySQL server and accept whatever information is returned.