CODEWITHSUNDEEP
mysqlsqlnested
Chiara
Chiara

Reputation: 73

Where is the error in my nested MySQL query?

I am guessing there is a mistake in the nested query however I cannot seem to find it myself. Here is the query:

Select student.sid, name 
from student join exam on exam.sid = student.sid
where student.sid in (select *
                from course join exam on cid=courseid 
                group by exam.sid
                having sum(credits) >= 20)

Thank you in advance!

Upvotes: 0

Views: 74

Answers (2)

Popeye
Popeye

Reputation: 35910

You can use the group by as follows:

select s.sid, s.name 
from student s 
Join exam e on s.sid = e.sid
Join course c on c.cid = e.courseid
group by s.sid, s.name
having sum(c.credits) >= 20

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270051

Yes, there is a mistake. The nested query is returning multiple columns but the comparison is to only a single column.

This will obviously generate an error. Presumably, you don't want the sum() but without sample data, desired results, and an explanation of what the query is supposed to be doing, it is hard to make a concrete suggestion.

The query may have other errors, but presumably, you intend something like this:

select s.sid, s.name 
from student s 
where s.sid in (select e.sid, 
                from course c join
                     exam e
                     on c.cid = e.courseid 
                group by e.sid
                having sum(c.credits) >= 20
               )

Upvotes: 0

Related Questions