Loop through column and assign 0/1 MySQL

Question

I have a MySQL database on consumers attending events. The database contains a users table, events table and checkins table which are all connected. But the database only contains info about users going to a certain event.

I want a query that loops through a couple of events and checks if a user went to one of them, and gives as a result:

Preferred result

I've tried with the following query:

select attend.idperson, attend.idmeeting,
    (select case when 
    attend.idmeeting = 2901 or
    attend.idmeeting = 9044 or
    attend.idmeeting = 9161 or
    attend.idmeeting = 2626
    then 1 else 0 end) as attended
from attend
join meetings on attend.idmeeting = meetings.idmeeting
join persons on attend.idperson = persons.id
where meetings.verified = 1;

But this returns as a result all meetings where the persons actually went, and assigns a 1 if one of the meetings in the select case when query was attended and a 0 for the rest. The goal is to predict attendance using logistic regression, and this solution makes all further user data biased.

Answer 1

You could build a list of all people and meetings, then left join the attendance details:

select distinct a1.*, 
       case
         when attend.idperson is null then 0 
         else 1 
       end as attended
from 
(
 select meetings.idmeeting, persons.id as p_id
 from meetings
 cross join persons
 where meetings.verified = 1
) a1
left join attend
  on attend.idmeeting = a1.idmeetings
  and attend.personid a1.p_id