mysql - Using GROUP_CONCAT -

i have 3 tables. tb_main table of entities. tb_boardmembers table of people. tb_boardmemberslink bridging table references other 2 ids , has start , end dates when person on board of entity. these dates incomplete.

i have been asked export part of report csv 1 row per entity per year in have list of board members year occupations in single field delimited newlines.

i don't need bml.entity in result added try debug. i'm getting 1 row expect 85. tried , without group , fact result same suggests misusing group_concat. how should construct result want?

select  group_concat( distinct concat(bm.first, ' ', bm.last,  if (bm.occupation != '', ' - ', ''), bm.occupation)  separator "\n") board, bml.entity   tb_main arfe, tb_boardmembers  bm, tb_boardmemberslink  bml year(bml.start) <= 2011  , (year(bml.end) >= 2011 or bml.end null) , bml.start > 0  , bml.entity = arfe.id group bml.entity order board 

there few issues query. main issue appears missing condition link board members link table, have cross join, i.e. returning every broadband member regardless of start/end dates, , assuming have 85 rows criteria matches, returning each board member 85 times. highlights reason switch ansi 89 implicit joins using, ansi 92 explicit join syntax. this article highlights reasons make switch.

so query become (i've had guess @ field names):

select  *    tb_main arfe         inner join tb_boardmemberslink  bml             on bml.entity = arfe.id         inner join tb_boardmembers  bm             on bm.id = bml.boardmemberid 

the next thing noticed query using functions in clause not efficient @ all, because of this:

where   year(bml.start) <= 2011  ,     (year(bml.end) >= 2011 or bml.end null) 

you operating year function twice every row, , removing possible chance of using index on bml.start or bml.end (if exist). yet again aaron bertrand has written a nice article highlighting practises when querying date ranges, target @ sql-server, principles still same, clause become:

where   bml.start <= '20110101' ,     (bml.end >= '20110101' or bml.end null) ,     bml.start > 0  

your final query should be:

select  bml.entity,         group_concat(distinct concat(bm.first, ' ', bm.last,              if (bm.occupation != '', ' - ', ''), bm.occupation)              separator "\n") board    tb_main arfe         inner join tb_boardmemberslink  bml             on bml.entity = arfe.id         inner join tb_boardmembers  bm             on bm.id = bml.boardmemberid   bml.start <= '20110101' ,     (bml.end >= '20110101' or bml.end null) ,     bml.start > 0 group bml.entity order board; 

example on sql fiddle