homepage Welcome to WebmasterWorld Guest from 54.204.94.228
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MYSQL 3 Tables Two Counts
Gibisan

10+ Year Member



 
Msg#: 4004712 posted 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

WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4004712 posted 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