This part of the query will do nothing to limit the result of the main query
SELECT `price`
FROM `prices`
WHERE `productid` = item.productid
AND `the_company` = '1'
AND `the_forex` = 'NZD'
AND `the_level` = '1'
) AS `price` , (
I am not sure why you are joining the table here and having subquery
LEFT JOIN `prices` ON prices.productid = item.productid
try this
SELECT item.productid, `quantity` , price, title ,
FROM `item`
JOIN `product` ON product.productid = item.productid
LEFT JOIN `prices` AS p ON prices.productid = item.productid
WHERE p.productid = item.productid
AND p.the_company = '1'
AND p.the_forex = 'NZD'
AND p.the_level = '1'
AND`uid` = 'deleted'
ORDER BY `productid`
LIMIT 0 , 30
Hope this helps
Mohammed Alsharaf
> From: [email protected]
> To: [email protected]
> Subject: [phpug] MySQL query weirdness
> Date: Fri, 2 Oct 2009 13:28:10 +1300
>
>
> I have the following query:
>
> SELECT item.productid, `quantity` , (
>
> SELECT `price`
> FROM `prices`
> WHERE `productid` = item.productid
> AND `the_company` = '1'
> AND `the_forex` = 'NZD'
> AND `the_level` = '1'
> ) AS `price` , (
>
> SELECT `title`
> FROM `product`
> WHERE `productid` = item.productid
> ) AS `title`
> FROM `item`
> JOIN `product` ON product.productid = item.productid
> LEFT JOIN `prices` ON prices.productid = item.productid
> WHERE `uid` = 'deleted'
> ORDER BY `productid`
> LIMIT 0 , 30
>
> which I am testing.
>
> This request should return the list of items in the user's cart (table
> 'item')
> along with the price (table 'prices') and the product title/description
> (table 'product')
>
> The problem is that it is returning 115 results when it should be returning
> 7,
> as there is 7 different items in the user's cart. It appears to be returning
> one line PER CURRENCY (16 currencies used) and ignoring the where
> specification for NZD. I have checked and confirmed that the 'prices' table
> does not have duplicate rows.
>
> I am totally lost.
>
> >
_________________________________________________________________
Feeling the financial pinch? Check on MSN NZ Money for a hand
http://money.msn.co.nz
--~--~---------~--~----~------------~-------~--~----~
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
-~----------~----~----~----~------~----~------~--~---
