MySQL:左连接和计数

Question

我正在使用此查询来获取所有位置:

SELECT
*
FROM
locations
WHERE
cat='Bars'
ORDER BY
locationname
ASC

现在我还想显示“事件”表中位置信息旁边的现有事件数量。

Table 'locations': (all unique locations)
locationid|locationname|address|cat

Table 'events': (different eventid's but maybe multiple hits for locationid)
eventid|locationid|eventname|eventdate

知道如何解决这个问题吗？

Answer 1

你可以试试这个:

SELECT COUNT(e.eventid), l.locationid
FROM locations l
LEFT JOIN events e ON l.locationId = e.locationId
WHERE cat='Bars'
GROUP BY l.locationId
ORDER BY .locationname ASC