One to many group_concatinate join

[monkle]

I have a 106 line query to draw in data from all over a relational database and dump into a single table for export. I am having issue with one section of the code, where I want to pull multiple rows (the first 5) into a delimited column for each row in the destination table.

Here is the SQL for that one column, found in the column list of the select query:

SUBSTRING_INDEX(
 (select group_concat( source_col separator '...' )
from table1
left join table2 using( key_1 ) 
left join table3 using( key_2 )
where col_3 < 'val'
group by ( key_1 ) ),
'...', 5) AS `col_dest`,

I believe that the sub query is returning multiple rows of delimited columns, instead of just the one. I don't know how to pass in key_1 from the main query. I think if I had that last piece that the query would be functioning as desired.

I have spent the last couple days searching for the needed piece in the documentation and in articles, but I have found nothing that deals with this. Any insight would be greatly appreciated.

 

[r937]

pass in key_1 from the main query?

like this --

SUBSTRING_INDEX(
(select group_concat( source_col separator '...' )
from table1
left join table2 using( key_1 )
left join table3 using( key_2 )
where col_3 < 'val'
and key_1 = foo.key_1 ),
'...', 5) AS `col_dest`,

if that don't work, you will have to break down and tell us what the query looks like




r937.com | rudy.ca

 

[monkle]

r937:

Thank you for your response.

After I made the post, I got inspired to try passing the variable in, however I never got the user defined variable to set correctly.

After fooling with it for some time I began to question the need for that set of data to be returned in this particular transaction. I did a rough evaluation on expected execution time. It came out at 19.6 hours because the values populating the column come from two tables that have approximately 2.4 million rows each. That isn't going to work since it will be a part of a daily scheduled task. After removing the one subquery, the execution time dropped below 3 seconds. Since this is just a temporary patch I found the benefits to far outweigh the loss.

Thanks again for the idea.