Getting a wellformed sql-result with habtm relations

Question

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.

Answer 1

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