当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql group_concat in subquery returns more than one row

问题描述:

I have the query, below, which should work. But, MySQL is giving me the error message 'Subquery returns more than one row'.

select e.episode_pk,

(select group_concat(d.fulldescription separator ', ')

from episode_rhythm er join diagnosis d on er.diagnosis_fk = d.diagnosis_pk

WHERE er.episode_fk = e.episode_pk group by d.fulldescription) as rhythmDesc

from episode e

WHERE e.patientid_fk='89976'

The entire purpose of using the group_concat in this query is to return only one row per 'episode'.

The outer select returns multiple rows.

The inner select, when run for a single episode table primary key, returns a single row as you would suspect:

select group_concat(d.fulldescription separator ', ')

from episode_rhythm er join diagnosis d on er.diagnosis_fk = d.diagnosis_pk

WHERE er.episode_fk = 234776 group by d.fulldescription

The inner select without the group_concat, when run for a single episode table primary key, may return multiple rows:

select d.fulldescription

from episode_rhythm er join diagnosis d on er.diagnosis_fk = d.diagnosis_pk

WHERE er.episode_fk = 234776

What do I need to do to get this to work as I would expect?

网友答案:

Your subquery within the SELECT is a bit doubtful.

I guess what you're looking for is:

SELECT
    e.episode_pk,
    GROUP_CONCAT(d.fulldescription separator ', ')
FROM episode_rhythm er
JOIN diagnosis d ON er.diagnosis_fk = d.diagnosis_pk
JOIN episode e ON er.episode_fk = e.episode_pk
WHERE e.patientid_fk='89976' 
GROUP BY e.episode_pk
分享给朋友:
您可能感兴趣的文章:
随机阅读: