kvolden kvolden - 1 month ago 9
SQL Question

SQL query exhausting many-to-many relationship based on single relationship?

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:

SELECT
businesses.rowid,
businesses.name,
GROUP_CONCAT(cities.name, ', ') as citylist
FROM businesses
JOIN associations ON businesses.rowid = associations.business_id
JOIN cities ON cities.rowid = associations.city_id
GROUP BY businesses.rowid


And this works perfectly. But what I want to do in practice, is to only list the businesses – in exactly the same way – that are associated to a given city. But I haven't found a way to this that will not make
GROUP_CONCAT(cities.name, ', ')
return only the given city, instead of all cities the business is associated with.

As a random example, if given the city "New York", I would want a result like this:

0: [rowid] = 42
[name] = Generic Business
[citylist] = New York, Jamestown, Albany
1: [rowid] = 31
[name] = Arbitrary Ltd.
[citylist] = Fulton, New York, Lockport


If it matters for the solution, I'm currently using SQLite 3, but I'm not completely opposed to changing if necessary.

Answer

Use a having clause;

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;

Notes:

  • Table aliases (the a, b, and c) make the query easier to write and to read.
  • I prefer to put all the unaggregated columns in the SELECT in the GROUP BY for clarity purposes.
Comments