How can i select multiple row and cascade them ?

Question

Let's say I have three tables:

table1 fields:

memberid | name

table2 fields:

interestId | interestName

table3 (used to make a relation between member and interest) fields:

memberid | interestId

and now I know I can user inner join to select one member's all interests.

But how can I cascade all the interests in a single row???

For example, I can select this result:

memberid   name    interstId   interestName
1         dennis   1            play basketball
1         dennis   2            music
1         dennis   3            moive

but the result i want to get is:

memberid   name    interests
1          dennis  play basketball, music, moive

How can I write the SQL query?

Thanks in advance!

Answer 1

since you didn't specify your database, I can advice to take a look at left (right) joins.

Answer 2

It depends on the DB you are using. Take a look at this question: Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list)

Answer 3

Depends on particular database. Maybe it will help you (using T-SQL and MS SQL Server) for a known memberid:

declare @result varchar(8000)

set @result = ''

declare @memberid int

set @memberid = 1

select @result = str(@memberid) + ' ' + (select name from table1 where memberid = @memberid) + ' '

select @result = @result + str(interestid) + ' ' + interest from ( select table2.interestid, table2.interestname from table3 inner join table2 on table2.interestid = table3.interestid where table3.memberid = @memberid ) t1

select left(@result, LEN(@result) - 1)

Answer 4

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

GROUP_CONCAT

just saw the comment on the dbms system, but it would work with mysql.

Answer 5

In SQL Server 2005 onwards, You can use XML Path() to concatenate values. It appears to be very performant too.

EDIT : Have tested the following and works

SELECT
    t1.memberid,
    t1.[name],
    ISNULL(STUFF(
      (
        SELECT
          ', ' + t2.interestName
          FROM 
              table2 t2
          INNER JOIN 
              table3 t3            
              ON 
              t2.interestId = t3.interestId
          WHERE 
              t3.memberid = t1.memberid
          FOR XML PATH('')
       ), 1, 2, ''
    ), 'None') As interests
FROM
    table1 t1
GROUP BY
    t1.memberid,
t1.[name]

Example code:

DECLARE @table1 TABLE ( memberid INT IDENTITY(1,1), name VARCHAR(25) )

INSERT INTO @table1 VALUES('dennis');
INSERT INTO @table1 VALUES('mary');
INSERT INTO @table1 VALUES('bill');

DECLARE @table2 TABLE ( interestId INT IDENTITY(1,1), interestName VARCHAR(25) )

INSERT INTO @table2 VALUES('play basketball');
INSERT INTO @table2 VALUES('music');
INSERT INTO @table2 VALUES('movie');
INSERT INTO @table2 VALUES('play hockey');
INSERT INTO @table2 VALUES('wine tasting');
INSERT INTO @table2 VALUES('cheese rolling');

DECLARE @table3 TABLE ( memberid INT, interestId INT )

INSERT INTO @table3 VALUES(1,1);
INSERT INTO @table3 VALUES(1,2);
INSERT INTO @table3 VALUES(1,3);
INSERT INTO @table3 VALUES(2,2);
INSERT INTO @table3 VALUES(2,4);
INSERT INTO @table3 VALUES(2,6);
INSERT INTO @table3 VALUES(3,1);
INSERT INTO @table3 VALUES(3,5);
INSERT INTO @table3 VALUES(3,6);

    SELECT
        t1.memberid,
        t1.[name],
        ISNULL(STUFF(
          (
            SELECT
              ', ' + t2.interestName
              FROM 
                  @table2 t2
              INNER JOIN 
                  @table3 t3            
                  ON 
                  t2.interestId = t3.interestId
              WHERE 
                  t3.memberid = t1.memberid
              FOR XML PATH('')
           ), 1, 2, ''
        ), 'None') As interests
    FROM
        @table1 t1
    GROUP BY
        t1.memberid,
     t1.[name]

Results

memberid    name                      interests
----------- ----------------------------------------------------------------------- 
1           dennis                    play basketball, music, movie
2           mary                      music, play hockey, cheese rolling
3           bill                      play basketball, wine tasting, cheese rolling

Answer 6

SELECT    t1.memberid,    t1.name,   
 STUFF( 
             ( SELECT          ', ' + interestName          
               FROM table2 t2  
               inner join table3 as t3 
               on t2.interestId = t3.interestId and t3.memberid = t1.memberid           
               FOR XML PATH('')      //use to merge the interests
             ), 1, 2, ''    
      ) As interests
FROM    table1

This works