Welcome to WebmasterWorld Guest from 50.17.16.177

Forum Moderators: open

Message Too Old, No Replies

help with count with left join

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

Junior Member

5+ Year Member

joined:May 16, 2009
posts: 86
votes: 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
3:01 pm on Aug 14, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:May 16, 2009
posts:86
votes: 0


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

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 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
3:54 pm on Aug 14, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:May 16, 2009
posts:86
votes: 0


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

Junior Member

5+ Year Member

joined:May 16, 2009
posts:86
votes: 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
6:00 pm on Aug 14, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 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