CODEWITHSUNDEEP
javasqlpostgresqljoin
Binge Eater
Binge Eater

Reputation: 23

column reference "group_id" is ambiguous when join

I have some troubles with join two tables:

Groups: group_id, group_name

Students: student_id, group_id, first_name, last_name

I wanna count how many students have each group and then output group name, but when i write like this:

public String findGroupsByStudentsNumber(int expectedStudentsNumber) {
        return " SELECT group_id, COUNT(student_id) AS Number_of_students " +
                "FROM students " +
                "LEFT JOIN groups ON group_id = groups.group_id " +
                "GROUP BY group_id " +
                "HAVING Number_of_students <=" + expectedStudentsNumber +
                " ORDER BY group_id";
    }

there is an error "column reference "group_id" is ambiguous".

Upvotes: 0

Views: 1321

Answers (1)

Blue Star
Blue Star

Reputation: 1952

Since both tables have a group_id column, you can't have a plain group_id in your query without specifying which one you're referring to. This is one way it could look:

SELECT students.group_id, COUNT(student_id) AS Number_of_students
FROM students
LEFT JOIN groups ON students.group_id = groups.group_id
GROUP BY students.group_id 
HAVING Number_of_students <= X
ORDER BY students.group_id

Upvotes: 1

Related Questions