select concat(guest.fname,' ',guest.lname) as guestof ,
if('a'=ra.regstatus,'Complete','Pending') as fullstatus ,
ra.* ,
concat(ev.sname,' -- ',ev.title) as event ,
max(if(rs.eventsurvey_id=1, rs.answer, '')) as 'defpop' ,
max(if(rs.eventsurvey_id=10, rs.answer, '')) as 'hphone' ,
max(if(rs.eventsurvey_id=3, rs.answer, '')) as 'hstreet1' ,
max(if(rs.eventsurvey_id=4, rs.answer, '')) as 'hstreet2' ,
max(if(rs.eventsurvey_id=5, rs.answer, '')) as 'hcity' ,
max(if(rs.eventsurvey_id=6, rs.answer, '')) as 'hstate' ,
max(if(rs.eventsurvey_id=7, rs.answer, '')) as 'hzip' ,
max(if(rs.eventsurvey_id=8, rs.answer, '')) as 'age' ,
max(if(rs.eventsurvey_id=9, rs.answer, '')) as 'optin' 
from regattendee ra 
LEFT OUTER JOIN regattendee guest ON ra.parent_id=guest.id 
LEFT OUTER JOIN session ev ON ra.event=ev.id 
LEFT OUTER JOIN regsurvey rs ON rs.regattendee_id=ra.id 
where ra.regstatus='a'
group by rs.regattendee_id order by ra.lname


### TO FILTER RESULTS BY ONLY PEOPLE OPTED IN, USE KEYWORD "HAVING"

select concat(guest.fname,' ',guest.lname) as guestof 
,if('a'=ra.regstatus,'Complete','Pending') as fullstatus 
,ra.* 
,concat(ev.sname,' -- ',ev.title) as event 
,max(if(rs.eventsurvey_id=1, rs.answer, '')) as 'defpop' 
,max(if(rs.eventsurvey_id=10, rs.answer, '')) as 'hphone' 
,max(if(rs.eventsurvey_id=3, rs.answer, '')) as 'hstreet1' 
,max(if(rs.eventsurvey_id=4, rs.answer, '')) as 'hstreet2' 
,max(if(rs.eventsurvey_id=5, rs.answer, '')) as 'hcity' 
,max(if(rs.eventsurvey_id=6, rs.answer, '')) as 'hstate' 
,max(if(rs.eventsurvey_id=7, rs.answer, '')) as 'hzip' 
,max(if(rs.eventsurvey_id=8, rs.answer, '')) as 'age' 
,max(IF(rs.eventsurvey_id=9, rs.answer, '')) AS 'optin'
from regattendee ra 
LEFT OUTER JOIN regattendee guest ON ra.parent_id=guest.id 
LEFT OUTER JOIN session ev ON ra.event=ev.id 
LEFT OUTER JOIN regsurvey rs ON rs.regattendee_id=ra.id 
where ra.regstatus='a'
group by rs.regattendee_id
having optin='y'
order by ra.lname