Compare two values in a row

select event_slots.id as event_slot_id, u.first_name, u.id AS user_id,

sum(case when next_round = 1 then 1 else 0 end) AS yes,
sum(case when next_round = 2 then 1 else 0 end) AS no

from event_slots

left join users as u on u.id = event_slots.selected_by_user_id 
left join audition_card_values as acv on acv.user_id = event_slots.selected_by_user_id 

where 
event_id = 1 and 
selected_by_user_id is not null

group by acv.user_id 

Ergebnis:

Now i need only the first row “first_name” = “Eins” because “yes” equals “no”. How can i make this where statemant? WHERE yes = no … doesn’t work.

Thanks for any help

Answer

I would phrase the query as:

select 
    ev.id as event_slot_id, 
    u.first_name, 
    u.id AS user_id,
    sum(next_round = 1) AS yes,
    sum(next_round = 2) AS no
from event_slots as ev
inner join audition_card_values as acv 
    on acv.user_id = ev.selected_by_user_id 
left join users as u 
    on u.id = ev.selected_by_user_id 
where e.event_id = 1
group by ev.id, u.first_name, u.id
having sum(next_round = 1) = sum(next_round = 2)

Rationale:

• the conditional expressions in the sum() can be simplified

• you can use a having clause for filtering; MySQL also supports reusing aliases defined in the select clause in the having clause, so you could spell this: having yes = no – but I prefer repeating the conditional expression, since this is standard SQL

• the left join on audition_card_values followed by a where ... is not null on the joining column can be rewritten as a simple inner join

• I fixed your group by clause; every non-aggregated column in the select clause must appear in the group by clause