Page 1 of 1

Multiple results returning

Posted: Sun Dec 26, 2004 12:20 am
by Steveo31

Code: Select all

SELECT inventory.store_name, inventory.product_name, inventory.upload_date, uploads.thumb_path, products.search_hits, products.hits 
FROM inventory, uploads, products 
WHERE CAST(inventory.store_name AS CHAR CHARSET utf8) = 'My Store''s $tuff!' 
AND products.product_name = uploads.product_name
AND products.id = uploads.id 
ORDER BY inventory.upload_date DESC
I would assume this would bring back something like:

Code: Select all

store_name    product_name    upload_date          thumb_path           search_hits           hits

>My Store's $tuff! 	Car 	2004-12-25 12:07:42 	members/Steveo31/car_thumb.jpg 	2 	2
>My Store's $tuff!  	Tools  	2004-12-25 12:06:35  	members/Steveo31/tools_thumb.jpg  	0  	1
>My Store's $tuff!  	Bread  	2004-12-25 12:05:28  	members/Steveo31/toaster_thumb.jpg  	4  	10
And not

Code: Select all

>My Store's $tuff!  	Car  	2004-12-25 12:07:42  	members/Steveo31/toaster_thumb.jpg  	0  	2
>My Store's $tuff! 	Car 	2004-12-25 12:07:42 	members/Steveo31/modelt_thumb.jpg 	0 	1
>My Store's $tuff! 	Car 	2004-12-25 12:07:42 	members/Steveo31/tools_thumb.jpg 	0 	1
>My Store's $tuff! 	Tools 	2004-12-25 12:06:35 	members/Steveo31/tools_thumb.jpg 	0 	1
>My Store's $tuff! 	Tools 	2004-12-25 12:06:35 	members/Steveo31/toaster_thumb.jpg 	0 	2
>My Store's $tuff! 	Tools 	2004-12-25 12:06:35 	members/Steveo31/modelt_thumb.jpg 	0 	1
>My Store's $tuff! 	Bread 	2004-12-25 12:05:28 	members/Steveo31/toaster_thumb.jpg 	0 	2
>My Store's $tuff! 	Bread 	2004-12-25 12:05:28 	members/Steveo31/modelt_thumb.jpg 	0 	1
>My Store's $tuff! 	Bread 	2004-12-25 12:05:28 	members/Steveo31/tools_thumb.jpg 	0 	1
Any ideas on this or the CAST() thing?

Posted: Sun Dec 26, 2004 12:39 am
by timvw
you should group by product_name and select sum(whatever hits) instead of hits...

Posted: Mon Dec 27, 2004 12:53 am
by Steveo31
Oh man...! So close!! But a step in the right direction nonetheless, thanks a ton man. But now the product_name and image_path don't correlate. Grouping by product_id gives the names correctly, but the image_paths are wrong. I'll check up on SQL stuff now....

Thanks again dude.