sql - Conditional statements within select for strings -

at moment query database find interactions within date parameter. join table interactionattendees join targetgroups table. interactionattendees tells me attended interaction, targetgroup tells me person, if part of target group such aboriginal or francophone.

i'm having trouble setting aboriginal , francophone columns. want set true if there attendees in targetgroup table.

select convert(date, getdate()) activitydate,         case when type = 0 'general'         when type = 1 'activity'         else 'task' end type,          case when indepth = 0 'false' else 'true' end indepth,          subject,         comments,         'false' aboriginal,        'false' francophone,        'false' female,        'false' youth,        'false' other  sarnia.dbo.interactions x  full outer join sarnia.dbo.interactionattendees y on x.id = y.interaction_id full outer join sarnia.dbo.targetgroups z on y.person_id = z.personid activitydate >= '2015-07-01' , activitydate <= '2015-09-30'  group activitydate, type, indepth, created, subject, comments 

for example

interaction table id  1  interaction attendee table id    interactionid    personid 1          1               5 2          1              10  targetgroups table id    personid    targetvalue 1        5           aboriginal 2       10           francophone 

so resulted table be

activity date    aboriginal    francophone      ----           true            true 

may ask how populate target group columns.

you can update query this: pivot in old fashioned way. based on previous questions assuming sql server db.

   max(iif(targetvalue = 'aboriginal', 'true', 'false')) aboriginal,    max(iif(targetvalue = 'francophone', 'true', 'false')) francophone 

if have sql server 2008 or older can use this

max(case when targetvalue = 'aboriginal' 'true' else 'false' end) aboriginal, max(case when targetvalue = 'francophone' 'true' else 'false' end) francophone