I have a database containing the two tables 'businesses' and 'cities', as well as a third many-to-many relationship table called 'associations' with only two fields in addition to its rowid: 'business_id' and 'city_id'. A business can cover several cities, and a city can be covered by several businesses.
Now I want to list each business together with the cities it is associated with. So I do this:
GROUP_CONCAT(cities.name, ', ') as citylist
JOIN associations ON businesses.rowid = associations.business_id
JOIN cities ON cities.rowid = associations.city_id
GROUP BY businesses.rowid
GROUP_CONCAT(cities.name, ', ')
0: [rowid] = 42
[name] = Generic Business
[citylist] = New York, Jamestown, Albany
1: [rowid] = 31
[name] = Arbitrary Ltd.
[citylist] = Fulton, New York, Lockport
SELECT b.rowid, b.name, GROUP_CONCAT(c.name, ', ') as citylist FROM businesses b JOIN associations a ON b.rowid = a.business_id JOIN cities c ON c.rowid = a.city_id GROUP BY b.rowid, b.name HAVING SUM(CASE WHEN c.name = 'New York' THEN 1 ELSE 0 END) > 0;
c) make the query easier to write and to read.
GROUP BYfor clarity purposes.