| help with count with left join
|
Sandro87
msg:4187027 | 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
msg:4187038 | 3:01 pm on Aug 14, 2010 (gmt 0) |
I forgot "group by " :)
|
rocknbil
msg:4187051 | 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
msg:4187060 | 3:54 pm on Aug 14, 2010 (gmt 0) |
I also need to show ids with count = 0 :)
|
Sandro87
msg:4187082 | 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
msg:4187089 | 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
|
|
|
