Search code examples
mysqlinner-joingroup-concat

Data transposition and concatenation, how to follow inner join condition?

I've a number of tables that are related between them with a foreign and primary key (basic) system.

I want to concat (comma separated) the values of the last "in-depth" table, respecting to some conditions.

Because it's hard to explain, I've made a draw that, I think, it's pretty explicit: image

Larger image can be found here: http://tinypic.com/r/2ngdaah/6

Note: tableE is already created with the column name corresponding to the names presented in tableD

Thank you!

EDITED (fiddle):

http://sqlfiddle.com/#!2/f28c8/1/0

Solution

  • I made a query but im not certain is correct. The reason is that the conditions you described used the symbol <= (less than or equal) and it appears to be just = (equal) because of the results below them.

    However, Try this:

    select * 
    from (select id, 
                   name,
                   max(case when e.tableD_id = 1 then  e.agg_values else null end) alpha,
                   max(case when e.tableD_id = 2 then e.agg_values else null end) beta,
                   max(case when e.tableD_id = 3 then e.agg_values else null end) gamma
            from tableA a, 
                (select b.tableA_id, 
                       b.tableD_id, 
                       group_concat(cast(c.value as char) order by c.id asc) agg_values
                  from tableB b,
                       tableC c
                 where c.tableB_id = b.id
                 group by b.tableD_id,b.tableA_id
                 having count(*) >= 3) e
              where a.id = e.tableA_id
              group by id, name
          ) n
          where n.alpha is not null 
            and n.beta is not null 
            and n.gamma is not null

    EDIT:

    Modified the query to support the conditions with >= instead of <=.

    SQLFIDDLE: http://sqlfiddle.com/#!2/f28c8/1/0

    SQLFIDDLE, first condition only: http://sqlfiddle.com/#!2/f28c8/4