homepage Welcome to WebmasterWorld Guest from 54.161.200.144
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved