SQL Many to Many query

Question

Hey i am trying to query my database tables that are set up in a many to many relationship with a table between. here is a quick erd of the tables in question

 Homes ----<  Home_Feature  >---- Features

I have tried creating the below sql query but is there a way to return one row for each home instead of the many that are returned here? Or do do i have to change my table structures to accommodate a better solution?

 SELECT homes.title, homes.description, homes.living_room_count, homes.bedroom_count, homes.bathroom_count, features.feature_name
 FROM homes
 INNER JOIN home_feature
 ON homes.home_id = home_feature.home_id
 INNER JOIN features
 ON home_feature.feature_id = features.feature_id;

Output from query:

    Title     Feature   ....
 1  House A   Balcony    
 2  House A   Pool
 3  House A   Garage
 4  House B   Air-Con

Thank you, any help appreciated!

  ____________________EDIT__________________________

Hey i greatly appreciate the help you guys have given so far and was wondering if i could have a little more help with regards to adding to this query and selecting columns from another table.

When i simply add the column of the other table in the SELECT statement and the table in the FROM clause the query seems to not work? the query im using is below but doesn't work. Thanks again for the help.

SELECT homes.title, homes.description, homes.living_room_count, homes.bedroom_count, homes.bathroom_count, homes.price, homes.sqft, home_type.type_name,
    listagg(features.feature_name, ',') WITHIN GROUP (ORDER BY features.feature_name)  features
FROM homes, home_type
INNER JOIN home_feature
ON homes.home_id = home_feature.home_id
INNER JOIN features
ON home_feature.feature_id = features.feature_id
GROUP BY homes.title, homes.description, homes.living_room_count, homes.bedroom_count, homes.bathroom_count, homes.price, homes.sqft;

I get this error:

ORA-00904: "HOMES"."HOME_ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"

how would you aggregate the features so you could have one home row per home? Count them, csv? — Jodrell, Jan 15 '13 at 10:34
[Possible duplicate](http://stackoverflow.com/questions/1120706/is-there-an-oracle-sql-query-that-aggregates-multiple-rows-into-one-row)? — Bernhard Barker, Jan 15 '13 at 10:51

score 3 · Accepted Answer · answered Jan 15 '13 at 11:00

Oracle 10g version:

SQL> SELECT homes.title, homes.living_room_count, homes.bedroom_count, homes.bathroom_count,
  2         wm_concat(features.feature_name) features
  3    FROM homes
  4         INNER JOIN home_feature
  5                 ON homes.home_id = home_feature.home_id
  6         INNER JOIN features
  7                 ON home_feature.feature_id = features.feature_id
  8   group by homes.title, homes.living_room_count, homes.bedroom_count, homes.bathroom_count;

TITLE   LIVING_ROOM_COUNT BEDROOM_COUNT BATHROOM_COUNT FEATURES
------- ----------------- ------------- -------------- ------------------------------
House A                 1             3              1 Balcony,Pool,Garage
House B                 1             2              2 Air-Con

and 11g we can use listagg:

SQL> SELECT homes.title, homes.living_room_count, homes.bedroom_count, homes.bathroom_count,
  2         listagg(features.feature_name, ',') within group (order by features.feature_name)  features
  3    FROM homes
  4         INNER JOIN home_feature
  5                 ON homes.home_id = home_feature.home_id
  6         INNER JOIN features
  7                 ON home_feature.feature_id = features.feature_id
  8   group by homes.title, homes.living_room_count, homes.bedroom_count, homes.bathroom_count;

TITLE   LIVING_ROOM_COUNT BEDROOM_COUNT BATHROOM_COUNT FEATURES
------- ----------------- ------------- -------------- ------------------------------
House A                 1             3              1 Balcony,Garage,Pool
House B                 1             2              2 Air-Con

score 1 · Answer 2 · answered Jan 15 '13 at 10:53

It depends on RDBMS you use for example for MySQL you can use GROUP_CONCAT for all features concatenation in one line:

SELECT homes.home_id,
       max(homes.title), 
       max(homes.description), 
       max(homes.living_room_count), 
       max(homes.bedroom_count), 
       max(homes.bathroom_count),
       GROUP_CONCAT(features.feature_name) features
 FROM homes
 INNER JOIN home_feature
 ON homes.home_id = home_feature.home_id
 INNER JOIN features
 ON home_feature.feature_id = features.feature_id
 GROUP_BY homes.home_id

In ORACLE (11g r2) you can use LISTAGG

SELECT homes.home_id,
       max(homes.title), 
       max(homes.description), 
       max(homes.living_room_count), 
       max(homes.bedroom_count), 
       max(homes.bathroom_count),
       LISTAGG(features.feature_name,',') 
          WITHIN GROUP(order by features.feature_name) as features
 FROM homes
 INNER JOIN home_feature
 ON homes.home_id = home_feature.home_id
 INNER JOIN features
 ON home_feature.feature_id = features.feature_id
 GROUP_BY homes.home_id

Also here is another way to emulate GROUP_CONCAT in oracle 10

SQL Many to Many query

2 Answers2