Getting a wellformed sql-result with habtm relations

Hi there…

I need to write an SQL-Query for a csv-export. I have a table "stores", "items_stores" and therefor "items". Stores HABTM Items. Now i want a result that has a column for the items.

Like this:

| Name       | Lat | Lng | Items         |
| Petes shop | 123 | 123 | Snacks, Pizza |
| Mama Pasta | 123 | 123 | Pasta, Pizza  |

You hopefully get the idea. Since I didn't use raw SQL in a long time, I have no clue how to do this. Damn you ORM. I thought of something like left join and then concat or so. Well no clue currently, really.

For the sake of simplicity let's assume the tables have those fields:

Stores: *id, name, lat, lng
ItemsStores: store_id, item_id
Items: *id, name

The Database is MySQL 5.x.

From stackoverflow

• Supposing that you use Sql Server 2005+, I would suggest that you use the following query to get the resulting table:

  select Name, Lat, Lng, stuff((select ', ' + i.name from item_store ist 
  join item i on ist.itemid = i.id where ist.storeid = s.id for xml path('')), 1,2,'') [Items] from store s
  

  Update.

  I don't know how to do that in MySql. But I found this similar question on SO.

  My best guess is this:

  select Name, Lat, Lng, gc
  from store s
  join
  (
  select storeid, GROUP_CONCAT(name order by name) gc
  from item_store ist 
  join item i on ist.itemid = i.id 
  where s.id = ist.storeid
  group by storeid
  ) t on t.storeid = s.id
  

  Nils Riedemann : Needed some adjustments to your code and then it worked! Awesome, thanks.