CODEWITHSUNDEEP
mysqlsql
user1013984
user1013984

Reputation: 53

SQL query - Group By doesn't work

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?

Thanks

Upvotes: 0

Views: 2265

Answers (3)

ben
ben

Reputation: 1936

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

Upvotes: 5

Czimi
Czimi

Reputation: 2534

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)

Upvotes: 2

Aducci
Aducci

Reputation: 26634

I think you are looking for GROUP_CONCAT

select 
  attendee_id, 
  group_concat(answer ORDER BY Id
               SEPERATOR ' ')
from wp_events_answer
group by attendee_id

Upvotes: 0

Related Questions