Querying normalized database, 3 tables

Question

I have three tables in a MySQL database:

stores (PK stores_id)
states (PK states_id)
join_stores_states (PK join_id, FK stores_id, FK states_id)

The "stores" table has a single row for every business. The join_stores_states table links an individual business to each state it's in. So, some businesses have stores in 3 states, so they 3 rows in join_stores_states, and others have stores in 1 state, so they have just 1 row in join_stores_states.

I'm trying to figure out how to write a query that will list each business in one row, but still show all the states it's in.

Here's what I have so far, which is obviously giving me every row out of join_stores_states:

SELECT states.*, stores.*, join_stores_states.* 
FROM join_stores_states 
JOIN stores 
ON join_stores_states.stores_id=stores.stores_id 
JOIN states 
ON join_stores_states.states_id=states.states_id

Loosely, this is what it's giving me:

store 1 | alabama
store 1 | florida
store 1 | kansas
store 2 | montana
store 3 | georgia
store 3 | vermont

This is more of what I want to see:

store 1 | alabama, florida, kansas
store 2 | montana
store 3 | georgia, vermont

Suggestions as to which query methods to try would be just as appreciated as a working query.

What flavor of database and version are you using? There are varied solutions based on the specific vendor's SQL. — Spencer Kormos, Jan 10 '12 at 17:22
This might be useful: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Scott Hunter, Jan 10 '12 at 17:25
Yup, this is `group_concat` in MySQL, but very kludgy in SQL Server. — Eric, Jan 10 '12 at 17:25

Amadan · Accepted Answer · 2012-01-10T18:00:31.737

If you need the list of states as a string, you can use MySQL's GROUP_CONCAT function (or equivalent, if you are using another SQL dialect), as in the example below. If you want to do any kind of further processing of the states separately, I would prefer you run the query as you did, and then collect the resultset into a more complex structure (hashtable of arrays, as a simplest measure, but more complex OO designs are certainly possible) in the client by iterating over the resulting rows.

SELECT stores.name,
  GROUP_CONCAT(states.name ORDER BY states.name ASC SEPARATOR ', ') AS state_names
FROM join_stores_states 
JOIN stores 
  ON join_stores_states.stores_id=stores.stores_id 
JOIN states 
  ON join_stores_states.states_id=states.states_id
GROUP BY stores.name

Also, even if you only need the concatenated string and not a data structure, some databases might not have an aggregate concatenation function, in which case you will have to do the client processing anyway. In pseudocode, since you did not specify a language either:

perform query
stores = empty hash
for each row from query results:
  get the store object from the hash by name
  if the name isn't in the hash:
    put an empty store object into the hash under the name
  add the state name to the store object's stores array

That's headed exactly towards where I want to go. It's listing only the first store and putting every single state that belongs to other stores into it, but that's a start. Thanks much — robbievasquez, Jan 10 '12 at 17:56

Querying normalized database, 3 tables

1 Answers1