help with count with left join

Forum Moderators: open

Message Too Old, No Replies

help with count with left join

Sandro87

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

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

Sandro87

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

I forgot "group by " :)

rocknbil

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

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

Sandro87

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

I also need to show ids with count = 0 :)

Sandro87

5:32 pm on Aug 14, 2010 (gmt 0)

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

syber

6:00 pm on Aug 14, 2010 (gmt 0)

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