MYSQL 3 Tables Two Counts

Forum Moderators: open

Message Too Old, No Replies

MYSQL 3 Tables Two Counts

Gibisan

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

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