Search code examples
mysqlgroup-concat

mysql select statement that includes group_concat

Ive a mysql statement like this

SELECT distinct mk.gene as gene, qs.rs as snp, CONCAT (qs.A1, qs.A2) as genotype
FROM dallas.QS_base qs  cross join   
dallas.staging mk 
     ON qs.rs = mk.rs 
WHERE qs.sampleID = 'mydna' 
order by gene ASC;

which returns this type of output

'ACE'    'RS4343',    'AA'
'ACTN3'  'RS1815739'  'TC'

from this type of table (dallas.staging)

'heart health', 'ACE', 'RS4343'
'skin health', 'ACE', 'RS4343'
'sports performance', 'ACE', 'RS4343'
'sports performance', 'ACTN3', 'RS1815739'
'longevity', 'ACTN3', 'RS1815739'

and this (dallas.QS_base)

'mydna','RS4343','A','A'
'mydna','RS1815739','T','C'

How should I change the mysql statement above to enable me to get this output? I believe I need to use group_concat command. 

'ACE'    'RS4343',    'AA' '(heart health, sports performance, skin health)'
'ACTN3'  'RS1815739'  'TC' '(sports performance, longevity)'
Solution

  • This query should do the job for you (note the distinct on mk.gene is not necessary as you are grouping by it):

    SELECT mk.gene as gene, qs.rs as snp, CONCAT (qs.A1, qs.A2) as genotype, GROUP_CONCAT(mk.condition) AS conditions
FROM QS_base qs  cross join   
staging mk 
     ON qs.rs = mk.rs 
WHERE qs.sampleID = 'mydna'
group by gene
order by gene ASC;

    Output:

    gene    snp         genotype    conditions
ACE     RS4343      AA          heart health,sports performance,skin health
ACTN3   RS1815739   TC          sports performance,longevity