Hi Michael,
If you're just trying to establish if you have a picture or not try this:
###
SELECT
product.productid,product.title,product.added,picture.w AS `width`,
picture.h AS `height`,picture.m AS `mime`,
IF( ISNULL( picture.pic ) || picture.pic = '' , 0 , 1 ) 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
###
That should save you a sub query and establish if you have pictures or not.
As for the left join, yes you want a left join. Have a read of this
http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php
Cheers,
Rob
Michael wrote:
> 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]
-~----------~----~----~----~------~----~------~--~---
