select s.submission_id,s.current_publication_id publication_id,
i.volume,coalesce(i.number,'') number,i.year,
pst.titol,
coalesce(aa.autors,'') autors,
coalesce(psy.copyrightyear,'') copyrightyear_actual,
coalesce(psh.copyrightholder,'') copyrightholder_actual,
coalesce(psl.licenseurl,'') licenseurl_actual
from submissions s
left join publication_settings psi on s.current_publication_id=psi.publication_id
left join issues i on psi.setting_value=i.issue_id
left join (select publication_id,replace(group_concat(distinct setting_value separator '|'),'"','""') titol from publication_settings where setting_name='title' and setting_value>'' group by publication_id) pst on s.current_publication_id=pst.publication_id
left join (select publication_id,group_concat(distinct setting_value separator '|') copyrightholder from publication_settings where setting_name='copyrightholder' and setting_value>'' group by publication_id) psh on s.current_publication_id=psh.publication_id
left join (select publication_id,group_concat(distinct setting_value separator '|') copyrightyear from publication_settings where setting_name='copyrightyear' and setting_value>'' group by publication_id) psy on s.current_publication_id=psy.publication_id
left join (select publication_id,group_concat(distinct setting_value separator '|') licenseurl from publication_settings where setting_name='licenseurl' and setting_value>'' group by publication_id) psl on s.current_publication_id=psl.publication_id
left join (select publication_id,group_concat(distinct a separator ', ') autors from
(select a.publication_id,a.author_id,s.locale,group_concat(s.setting_value order by s.setting_name desc separator ' ') a
from authors a left join author_settings s on a.author_id=s.author_id
where s.setting_name in ('givenname','familyname') and s.setting_value>''
group by a.author_id,s.locale) aa
group by publication_id) aa on s.current_publication_id=aa.publication_id
where s.context_id=89
and psi.setting_name='issueid'
order by i.issue_id,s.submission_id;