Michael Actually it returns 2 pices of info, the count of 'pictures' for this product and the name of an 'picture' (with some categotry conditions and display = 1) The reason for the left join is so that all products turn up (even if they have no pictures)
Not a particularly efficient query as it is structured IMHO Neven > Opps, didn't word this too well. The question should be - is there any reason > a LEFT join is required as opposed to 'join'. > > Statement (to obtain a paginated list of products) > > SELECT product.productid,product.title,product.added,picture.w AS `width`, > picture.h AS `height`,picture.m AS `mime`, (SELECT count(`pic`) FROM > `picture` > WHERE picture.productid=product.productid AND picture.pic !='') AS `pcx` FROM > `product` LEFT JOIN `picture` ON product.productid=picture.productid > WHERE categoryid='$categoryid' AND `categoryid` IN $catlist AND `display`='1' > ORDER BY `title` ASC LIMIT $start,$products > > Tables: > 'product' - product details > 'picture' - pictures stored in database as binary > > It does a subquery to astertain if a picture exists (else sets a NULL field > so > the front end application skips the display picture code), though I am > wondering what is the LEFT JOIN required for? > > We aren't actually trying to retrieve the picture BLOB in this query, just > checking if it exists. > > Michael > > > > > --~--~---------~--~----~------------~-------~--~----~ NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
