SQL query to mysql (Subquery does not recognize outer table)

Question

I know mysql does not recognize the outer table from a subquery, but sadly I have no idea how to solve this.

First the table structure, I have three tables (m to n):

Table1: dance

 TID | Name_Of_The_Dance   
 12  | Heute Tanz ich

Table2: dancer

TAID | Name_Of_Dancer
 1 | Alex Womitsch  
 2 | Julian Berger

Table3 (Referencing table): dance2dancer

TID | TAID   
 12 | 213   
 12 | 345 

What do I want to achieve (output):

TID | AllDancerWhoDance  
12 |  213---,345---,0---,0---,0---,0---,0---,0---,0---,0--- 

Every output should have the dance TID and all dancer who dance in this dance. But AllDancerWhoDance should be filled up with "0---", when we have less than 10 dancer. When we have more than 10 dancer in this dance, the query should decrease the string to a maximum of 10 dancer.

More examples to unterstand: A dance with 4 dancers should be filled up with 6 zeros:

9 | 213---,345---,111---,459---,0---,0---,0---,0---,0---,0---   

Do we have a dance with more than 10 dancers the query should decrease it to 10:

9 | 213---,345---,111---,459---,333---,444---,445---,222---,192---,490--- (NO more zeros or dancer TAIDs)

And here is my query:

select dancer.tid, 
    IF(count(dancer.taid) <= 10, 
        CONCAT_WS("",GROUP_CONCAT(dancer.taid,"&&&"), REPEAT(";0",10-count(dancer.taid)))
      , (SELECT GROUP_CONCAT(a.taid,"&&&") from (SELECT ttt.taid from dance2dancer ttt inner join dance taenz on ttt.tid = taenz.tid where ttt.tid = dance.tid LIMIT 10) as a)
      ) AS "AllDancerWhoDance"
from dance inner join dance2dancer tt on dance.tid = tt.tid inner join dancer on tt.taid = dancer.taid group by dance.tid

I think it would work but the problem is that the subquery does not look into the outer table and the "where" clause is not working:
where ttt.tid = dance.tid

And now my questions:
How can I achieve this sql query working in mysql?

Thank you

//UPDATE
Because many people asking for the front end code and why I would need this query: We have an 22 years old software, that needs this data in such a format. It was programed from a company which does not exists anymore and we do not have any source code of this program. We have changed the database and website to this new datamodel (m:n) but the old program still needs the data in the old format. Therefore I need this strange query. And yes, we are working on a new program as well.

Answer 1

To get 10 dancer for a given dance do this :

select D.Name_Of_Dancer 
from  dancer DR, dance2dancer DD
where DD.taid = DR.taid 
groupby DD.tid
Limit 10

You can also add Desc or ASC to sort the list of dancer list and extract first 10 dancer.

But to get minimum 10 dancer in a list even if it doesn't exist. Its not possible by mysql. What I mean is show me the code on front end how are you handling, what we can do is count the number of results. if results are 4 we can simply use a loop to create 6 other entries to get total 10 dancer. But I dont recommend handling them from mysql end. Its a bad practice.

Answer 2

The reason for your error is that you cannot access outer columns from inside a derived table (your from (...) as a). To do it, you have to write something like (...) as a where a.tid = dancer.tid, so you put the where outside of the derived table; but you obviously have to rewrite a in a way to have tid as a column.

In your case, it would be more complicated to fix your code, so I wrote you an (easier) new one:

select tid,
  CONCAT_WS("",
  (SELECT GROUP_CONCAT(d2d2.taid,"---") 
   from dance2dancer d2d2
   where d2d2.tid = d2d.tid 
   group by d2d2.tid limit 10),
  REPEAT(",0---",10-count(distinct d2d.taid))
  ) as `AllDancerWhoDance`
from dance2dancer d2d
group by d2d.tid

And a warning: this kind of query (using group_concat or concat in a column) is only to be used in a final step to format the data you want to display. If you plan to use it in another query, e.g. something like select * from dancer where INSTR('213---,345---,111---', taid) > 0, please don't, just rewrite it.

Update without using limit 10:

Although limit 10 should work with group_concat (it works for me), in case it doesn't work for you, you can of course just concat everything and then take the first 10 entries of the concated string afterwards. It would actually simplify the query, since you don't need the subquery anymore (that only was there to have the limit in the first place), but might generate a large temporary string (before substring_index) if you have a dance with A LOT of dancers.

select tid,
  SUBSTRING_INDEX(CONCAT_WS("",
  GROUP_CONCAT(d2d.taid,"---"),
  REPEAT(",0---",10-count(distinct d2d.taid)),
  ','
  ), ',',10) as `AllDancerWhoDance`
from dance2dancer d2d
group by d2d.tid;

or calculate a rownumber beforehand and just take the first 10 rows per tid:

select d2d.tid, CONCAT_WS("",
  GROUP_CONCAT(d2d.taid,"---"),
  REPEAT(",0---",10-d2d.cnt)
  ) as `AllDancerWhoDance`
from 
(select tid, taid, 
      (select count(*) 
       from dance2dancer d2d4
       where d2d4.tid = d2d3.tid
       and d2d4.taid <= d2d3.taid
      ) as rownum,
      (select count(*) 
       from dance2dancer d2d4
       where d2d4.tid = d2d3.tid
      ) as cnt
      from dance2dancer d2d3
) as d2d
where d2d.rownum <= 10
group by d2d.tid;