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!

What RDBMS? SQL Server 2000, 2005, MySQL, etc – Russ Cam Aug 02 '09 at 10:32 — Russ Cam, Aug 02 '09 at 10:32

score 1 · Answer 1 · edited May 23 '17 at 10:32

1

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)

edited May 23 '17 at 10:32

Community

1
1

answered Aug 02 '09 at 10:36

Roee Adler

33,434
32
105
133

Russ Cam · Accepted Answer · 2009-08-03T10:01:16.693

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

Column 't3.interestId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. What's wrong? — MemoryLeak, Aug 02 '09 at 16:03
Hi Dennis, have fixed the code now and provided a working example :) — Russ Cam, Aug 03 '09 at 10:01

score 0 · Answer 3 · answered Aug 02 '09 at 10:32

0

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

answered Aug 02 '09 at 10:32

iBiryukov

1,730
4
19
30

score 0 · Answer 4 · edited Oct 07 '17 at 00:41

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)

score 0 · Answer 5 · answered Aug 02 '09 at 12:01

0

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.

answered Aug 02 '09 at 12:01

Beffa

915
1
8
18

score 0 · Answer 6 · answered Aug 02 '09 at 23:29

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

How can i select multiple row and cascade them?

6 Answers6