Welcome to WebmasterWorld Guest from 54.198.222.129

Forum Moderators: open

Message Too Old, No Replies

help with count with left join

     
2:12 pm on Aug 14, 2010 (gmt 0)

5+ Year Member



hello

i have 2 tables

1st table called services has id_service, name, date
2nd table called services_images has id_img, img_name,id_service

What I wanna do is get list of services with a row called "num_images" that counts how many imgages there are per service

I tried this

SELECT COUNT(services_images.id_img) as num_images, services.* FROM services
LEFT JOIN services_images ON (services_images.id_service = services.id_service) ORDER BY service.id_service


It doesnt work and I dont know how to fix it. This will return just ONE service (the first id_service: 1) with as num_images the count of all the rows in services_images! Even if change "services_images.id_service = services.id_service" to "services_images.id_service = 2" it stills shows the first id! So this rules is not even considered

Thanks
3:01 pm on Aug 14, 2010 (gmt 0)

5+ Year Member



I forgot "group by " :)
3:35 pm on Aug 14, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Yes but keep in mind a left join will return results - and a count - whether or not there are matching entries in the joined table (Ex.: field1, field2, NULL). If you want to only count rows that have matching entries do this, and you won't need a group by.

SELECT COUNT(services_images.id_img) as num_images, services.* FROM services, services_images where services_images.id_service = services.id_service ORDER BY service.id_service
3:54 pm on Aug 14, 2010 (gmt 0)

5+ Year Member



I also need to show ids with count = 0 :)
5:32 pm on Aug 14, 2010 (gmt 0)

5+ Year Member



New problem let's say now that (with the same columns) I have to, with one query (if possible), return 2 arrays

1 with fields from one id from table "services"
2 with fields from all the images related to the selected id in "services" from table "services_images"

If I cant do it in mysql how can I arrange this in PHP, the only thing I can think of is 2 queries
6:00 pm on Aug 14, 2010 (gmt 0)

10+ Year Member



Something like this?


SELECT services.*,
(SELECT COUNT(services_images.id_img)
FROM services_images
WHERE id_service = services.id_services) AS num_images,
services_images.id_img, services_images.img_name
FROM services
LEFT JOIN services_images
ON (services_images.id_service = services.id_service)
ORDER BY service.id_service
 

Featured Threads

Hot Threads This Week

Hot Threads This Month