Welcome to WebmasterWorld Guest from 54.167.213.22

Forum Moderators: open

Message Too Old, No Replies

MYSQL 3 Tables Two Counts

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

10+ Year Member



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;

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

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



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. :)

 

Featured Threads

Hot Threads This Week

Hot Threads This Month