Welcome to WebmasterWorld Guest from 54.167.209.198

Forum Moderators: open

Message Too Old, No Replies

MYSQL 3 Tables Two Counts

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

Junior Member

10+ Year Member

joined:Oct 6, 2004
posts:54
votes: 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;

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

Senior Member from US 

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

joined:Dec 9, 2003
posts:3416
votes: 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. :)

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members