[thelist] sql statement

Dan McCullough dan.mccullough at gmail.com
Wed Mar 30 14:52:47 CST 2005


Actually I just noticed something.  name is returning the related
product name.  so the name of the product from the short field JOIN is
showing in both

SELECT 	P.productid, 
		P.name, 
		P.action, 
		P.lg_picture, 
		P.description,
		P.short,
        S.abrv,
        P2.name
 FROM products P
   INNER JOIN prod_size PS
     ON P.productid = PS.productid
   INNER JOIN sizes S
     ON PS.sid = S.sid
   LEFT OUTER JOIN products P2
     ON P.short = P2.productid
 WHERE P.productid = '27'
 ORDER BY P.sort_order					
p.productid|p.name|p.action|p.lg_picture|p.description|p.short|s.abrv|p2.name
27|Prod2|photo.jpg|prodphoto.jpg|Description...|2|YS|Prod2 
27|Prod2|photo.jpg|prodphoto.jpg|Description...|2|YM|Prod2
27|Prod2|photo.jpg|prodphoto.jpg|Description...|2|YL|Prod2 
27|Prod2|photo.jpg|prodphoto.jpg|Description...|2|S|Prod2
27|Prod2|photo.jpg|prodphoto.jpg|Description...|2|M|Prod2 
27|Prod2|photo.jpg|prodphoto.jpg|Description...|2|L|Prod2 
27|Prod2|photo.jpg|prodphoto.jpg|Description...|2|XL|Prod2 
27|Prod2|photo.jpg|prodphoto.jpg|Description...|2|XXL|Prod2 
27|Prod2|photo.jpg|prodphoto.jpg|Description...|2|XXXL|Prod2 

So what is going in both the p.name and p2.name is the the p2.name
data, the sizes, the short are both for the p table


On Wed, 30 Mar 2005 12:29:37 -0800, Ed McCarroll <Ed at comsimplicity.com> wrote:
> > > ... because size is returning more then one result, because the
> > > sizes for this product is more then one.  what i got back was
> > > the product listed 9 times, one for each size in the product.
> 
> If you know what size you want, add it to your WHERE clause.
> 
> If you don't, what do you want the query to do?
> --
> Ed McCarroll                                  (310) 838-4330
> Ed at ComSimplicity.com            http://www.ComSimplicity.com
> --
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>


More information about the thelist mailing list