CODEWITHSUNDEEP
sqlpostgresql
nguyli03
nguyli03

Reputation: 51

missing FROM-clause entry for table when using string_agg

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!

Upvotes: 0

Views: 2251

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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;

Upvotes: 2

Related Questions