You know this is a PHP users group, right? You've misunderstood what a JOIN does. The columns from the joined tables are added to the query result, so you don't need the sub- queries. The filters in the sub-query only apply to the sub-query, not the overall result.
Try: SELECT item.productid, `quantity` , `prices`.`price`, `product`.`title` FROM `item` JOIN `product` ON product.productid = item.productid LEFT JOIN `prices` ON prices.productid = item.productid WHERE `uid` = 'deleted' AND `the_forex` = 'NZD' ORDER BY `productid` LIMIT 0 , 30 Also, read this: http://dev.mysql.com/doc/refman/5.0/en/join.html On Oct 2, 1:28 pm, Michael <[email protected]> wrote: > 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. --~--~---------~--~----~------------~-------~--~----~ NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
