CODEWITHSUNDEEP
sql
Max_S
Max_S

Reputation: 135

Group by in nested subquery

I've got two tables:

Researcher (ResId, Fname, Gname)
Book (bookID, ResID*, Descr)

I need to use IN (one of the condition of my assignment) statement to show a list of researchers that have more than 5 books.

I've got the following SQL query that seems to be logic but it gives an error. The subquery on its own works just fine:

select  ResId, Fname
from Researcher
where ResId in
   (select Book.ResId, count(*)
   from Book
   group by Book.ResId
   having count(*) > 5
   )

Upvotes: 0

Views: 69

Answers (3)

user3522371
user3522371

Reputation:

Only remove count(*) like this:

select  ResId, Fname
from Researcher
where ResId in
   (select Book.ResId 
   from Book
   group by Book.ResId
   having count(*) > 5
   )

Upvotes: 1

KekuSemau
KekuSemau

Reputation: 6852

where ResId in
   (select Book.ResId, count(*)

When you use IN (Select..., you can only select one column in the sub query.
Change the above to:

where ResId in
   (select Book.ResId

only.

Upvotes: 1

Chief Wiggum
Chief Wiggum

Reputation: 2934

Does that work as expected?

Select  ResId, Fname
From Researcher r
Inner Join Book b on b.ResId = r.ResId
Group By r.ResId, r.FName
Having count(b.*) > 5

Upvotes: 1

Related Questions