Take the 2-minute tour ×

I have a table "Answer" like this:

Id, Registration_id, attendee_id, question_id, answer
12   Reg02                1             1        Paul
13   Reg02                1             2        Smith
14   Reg03                2             1        Rachel
15   Reg03                2             2        Silva

My query is:

Select attendee_id, answer
From wp_events_answer;                            

As a result I obtain this:

attendee_id  answer
    1         Paul
    1         Smith
    2         Rachel      
    2         Silva

But i'd like to have this:

 attendee_id  FirstName   Name
    1         Paul        Smith
    2         Rachel      Silva  

I added Group By attendee_id. But it still doesn't work. As a result it displays only this:

 attendee_id    FirstName
 1              Paul
 2              Rachel

Can you please tell me what's wrong?


share|improve this question
I think this group by is working fine... –  evilone Nov 29 '11 at 19:09
What you want is not the feature of group by –  Framework Nov 29 '11 at 19:09
You cannot group by one column and select other non-aggregated column values. I think mySql lets you, but the results you get will be random at best. –  Mike Christensen Nov 29 '11 at 19:11
You need to make two queries, filter by question_id and join by attendee_id. –  morja Nov 29 '11 at 19:11
Smells like meta DB to me. Sigh... stackoverflow.com/a/7340554/570191 –  Adrian Carneiro Nov 29 '11 at 19:17

3 Answers 3

You don't need a group, you need a self-join.

SELECT f.attendee_id, f.answer as FirstName, l.answer as Name
FROM wp_events_answer f
    JOIN wp_events_answer l ON f.attendee_id = l.attendee_id
WHERE f.question_id = 1 AND l.question_id = 2
share|improve this answer

If you need to have multiple rows from a table to show up in the result you need to join the table with itself.

select fn.attendee_id, fn.answer as FirstName, ln.answer as lastName FROM  wp_events_answer as fn INNER JOIN wp_events_answer as ln WHERE (fn.attendee_id = ln.attendee_id) and (fn.question_id = 1) and (ln.question_id=2)
share|improve this answer

I think you are looking for GROUP_CONCAT

  group_concat(answer ORDER BY Id
               SEPERATOR ' ')
from wp_events_answer
group by attendee_id
share|improve this answer

Your Answer


