homepage Welcome to WebmasterWorld Guest from 23.21.9.44
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

    
MYSQL 3 Tables Two Counts
Gibisan




msg:4004714
 10:12 am on Oct 10, 2009 (gmt 0)

Is there a nice way of getting a single query from the following two queries? I am after a list of locations and a count of video and photos per location. They work fine seperately but I cannot seem to get them together in a single query.

SELECT spot.id, spot.name, spot.url, spot.lat, spot.lng, count(img.id) as photos
FROM s_spots as spot
LEFT JOIN s_image_gallery as img ON spot.id = img.spotid
WHERE polygonid=312 GROUP BY spot.id ORDER BY spot.name ASC;

SELECT spot.id, spot.name, spot.url, spot.lat, spot.lng, count(vid.id) as video
FROM s_spots as spot
LEFT JOIN s_video as vid ON spot.id = vid.spotid
WHERE polygonid=312 GROUP BY spot.id ORDER BY spot.name ASC;

 

whoisgregg




msg:4039012
 8:41 pm on Dec 7, 2009 (gmt 0)

Something like this may work:

SELECT *
FROM s_spots, s_video, s_image_gallery
WHERE polygonid=312
AND s_spots.id = s_image_gallery.spotid
AND s_spots.id = s_video.spotid
GROUP BY s_spots.id ORDER BY spot.name ASC;

If that's not the output you expected, try this:

SELECT *
FROM (
s_spots AS spot LEFT JOIN s_image_gallery AS img ON spot.id = img.spotid
)
LEFT JOIN s_video AS vid ON spot.id = vid.spotid
WHERE s_spots.polygonid=312
GROUP BY spot.id
ORDER BY spot.name ASC

If that still isn't right, please post back with more details. :)

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