I have this query to try to search for the course that fulfills 3 requirements.
select
course.id,
course.title,
course.number,
string_agg(requirement.description,'; ' order by requirement.description)
from course
join
(
select course.id, course.title, course.number
from requirement join course_requirement
on (requirement.id=course_requirement.requirement)
join course on (course.id=course_requirement.course)
where requirement.description='Human Behavior'
) as c1
on (c1.id=course.id)
join
(
select course.id, course.title, course.number
from requirement
join course_requirement
on (requirement.id=course_requirement.requirement)
join course
on (course.id=course_requirement.course)
where requirement.description='Intercultural'
) as c2
on (c1.id=c2.id)
join
(
select course.id, course.title, course.number
from requirement
join course_requirement
on (requirement.id=course_requirement.requirement)
join course
on (course.id=course_requirement.course)
where requirement.description='Religion'
) as c3
on (c1.id=c3.id)
group by course.id, course.title, course.number;
However, I receive this message: ERROR: missing FROM-clause entry for table "requirement" LINE 1: ...course.id, course.title, course.number,string_agg(requiremen... Can you tell me where did I get the query wrong?
Thank you so much!
I don't see the point of the three joined subqueries, which differ only in the requirement description in the WHERE clause. Instead, you might try a single query which uses WHERE IN to capture the three descriptions you want in your result set. This also fixes the problem you had with string_agg(), where you were trying to aggregate something from a subquery not reachable in the outer query.
Try the following query:
select
t1.id,
t1.title,
t1.number,
string_agg(t3.description, '; ' order by t3.description)
from course t1
inner join course_requirement t2
on t1.id = t2.course
inner join requirement t3
on t2.requirement = t3.id
where t3.description in ('Human Behavior', 'Intercultural', 'Religion')
group by t1.id, t1.title, t1.number;