I am trying to return a list of course IDs that contain three components: assignments, discussions, and a syllabus. I want my results to have only one row per course ID with either Y's or N's indicating whether those courses have assignments, discussions, and a syllabus. However, I am getting every unique combination of Y's and N's for a given course ID.
How do I return only one row per course ID?
select distinct cm.course_id as Course,
case
when exists (select 1 where gt.name ilike 'Assignment%')
then 'Y'
else 'N'
end as are_there_assignments,
case
when exists (select 1 where gt.name ilike 'Discussion%')
then 'Y'
else 'N'
end as are_there_discussions,
case
when exists (select 1 where ct.label ilike '%syllabus%' )
then 'Y'
else 'N'
end as is_there_a_syllabus
from course_main cm
left join course_course cc
on cm.pk1 = cc.crsmain_pk1
inner join gradebook_main gm
on cm.pk1 = gm.crsmain_pk1
inner join gradebook_type gt
on gm.gradebook_type_pk1 = gt.pk1
inner join course_toc ct
on cm.pk1 = ct.crsmain_pk1
where cc.crsmain_parent_pk1 is NULL
and (cm.course_id ilike '%SPF-2020-C%'
or cm.course_id ilike '%SPF-2020-S%'
or cm.course_id ilike '%SP2-2020-C%'
or cm.course_id ilike '%SP2-2020-S%'
or cm.course_id ilike '%SP2-2020-A%'
or cm.course_id ilike '%SPF-2020-A%')
order by cm.course_id;
Here are my current results:
I think that you can do conditional aggregation. Starting from your existing query, that would look like:
select
cm.course_id,
max(case when gt.name ilike 'Assignment%' then 'Y' else 'N' end) are_there_assignments,
max(case when gt.name ilike 'Discussion%' then 'Y' else 'N' end) are_there_discussions,
max(case when ct.label ilike '%syllabus%' then 'Y' else 'N' end) is_there_a_syllabus
from ...
where ...
group by cm.course_id
order by cm.course_id