How to concatenate Multiple Rows from Multiple Tables Efficiently

Question

I have multiple tables with a foreign key from a main table. The tables are like this:

Sisters

MainID    IDNO   ....
111111       1   ....          
111111       1   ....          
111111       1   ....
222222       1   ....          
111111       2   ....

Brothers

MainID    IDNO   ....
111111     555   ....          
111111     333   ....          
111111     111   ....
222222     222   ....          
111111     321   ....

Uncles

MainID    IDNO   ....
111111     561   ....          
111111     121   ....          
111111     331   ....
222222     451   ....          
111111     672   ....

I need to concatenate all IDNos in but I can't seem to get all as for example maybe I can't get 672

SELECT 
    ',' + S1.IDNo + ',' + B1.IDNo + ',' + U1.IDNo AS [text()] 
FROM 
    Sisters S1, Brothers B1, Uncles U1, MainTable 
WHERE 
    D1.MainID = MainTable.ID 
    AND S1.MainID = MainTable.ID 
    AND B1.MainID = MainTable.ID 
FOR XML PATH('')

I tried to seperate tables and concanate later but it is getting much slower that way. What can I do?

Expected output:

,1,1,1,1,2,555,333,111,222,321,561,121,331,451,672

FYI: maintable has around 200.000 records and the sub-tables has around 600.000 each. — sertsedat
– sertsedat, Commented Jul 28, 2016 at 13:48
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Jul 28, 2016 at 13:51
Presumably, you want union all, but without sample results, that is only speculation. — Gordon Linoff
– Gordon Linoff, Commented Jul 28, 2016 at 13:53
It's not really about speed - but about readability and thus maintainability, and avoiding unexpected cross joins (cartesian products) .... — marc_s
– marc_s, Commented Jul 28, 2016 at 13:53

objectNotFound · Accepted Answer · 2016-07-28 19:55:21Z

1

Based on the expected output it appears that the MainID column in each of the 3 tables has no say in the output (if so) then this should work for you though Iam not sure it will scale to handle 600K+ records. Out of curiosity why would you want to concatenate such a large list of values ?

Declare @x as varchar(max) = ''

SELECT @x = @x + ',' + CAST(A.IDNO as varchar)  FROM
(
select 1 as IDNO UNION ALL
select 2 as IDNO UNION ALL
select 3 as IDNO UNION ALL
select 4 as IDNO 
) A -- Pretend this is your Sisters tables
FOR XML PATH('')
PRINT @X -- Just for Debugging Purposes

SELECT @x = @x + ',' + CAST(B.ID as varchar)  FROM
(
select 55 as IDNO UNION ALL
select 66 as IDNO UNION ALL
select 77 as IDNO UNION ALL
select 88 as IDNO 
) B -- Pretend this is your Brothers tables

PRINT @X -- Just for Debugging Purposes

SELECT @x = @x + ',' + CAST(C.IDNO as varchar)  FROM
(
select 555 as IDNO UNION ALL
select 666 as IDNO UNION ALL
select 777 as IDNO UNION ALL
select 888 as IDNO 
) C -- Pretend this is your Uncles tables


PRINT @X -- Final Output 
SELECT @X as XML_Output FOR XML PATH('')

Output:

,1,2,3,4
,1,2,3,4,55,66,77,88 
,1,2,3,4,55,66,77,88,555,666,777,888 -- Final Result

<XML>,1,2,3,4,55,66,77,88,555,666,777,888</XML> -- XML Output

So in your Case you could do something like this (Again Not sure how it will behave on large tables):

Declare @x as varchar(max) = ''
SELECT  @x = @x + ',' + CAST(A.IDNO as varchar)  FROM
(
select MainID ,   IDNO FROM SISTERS  UNION ALL
select MainID ,   IDNO FROM BROTHERS UNION ALL
select MainID ,   IDNO FROM UNCLES
) A


SELECT @X as XML_Output FOR XML PATH('')

edited Jul 28, 2016 at 19:55

answered Jul 28, 2016 at 15:58

objectNotFound

1,8232 gold badges20 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sertsedat Over a year ago

well, I want this list because the client wants to search a main item with idno's from sub-tables. though I don't know if there is an another way, the most reasonable thing that came to my mind was this. Example; if client search 555 in IDs I should display main item with ID no 111111, given the situation in my question.

Collectives™ on Stack Overflow

How to concatenate Multiple Rows from Multiple Tables Efficiently

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related