CODEWITHSUNDEEP
sql
sogyals429
sogyals429

Reputation: 375

Select papers having three or more authors

I have two tables author and paper. How do I find the paper which has three or more authors? I tried using

select distinct acnum,panum from author Where acnum >3 order by acnum;

and

select p.panum,p.title from paper p Join author a on a.panum = p.panum Group by p.panum,p.title Having count(a.acnum) >3;

am I doing it wrong? The relation between the author and paper is the paper number.

Upvotes: 0

Views: 93

Answers (2)

Zorkolot
Zorkolot

Reputation: 2027

You said in the comments:

 i used select p.panum,p.title from paper p Join author a on a.panum = p.panum Group by p.panum,p.title Having count(a.acnum) >3

Lets assume that's the exact query you used. You did not do a COUNT in the select, but use HAVING. HAVING analyzes the aggregation that should be in the select, but there is none. You just need to put a COUNT in the select:

SELECT p.panum
      ,p.title
      ,COUNT(*) as "thecount"
  FROM paper P INNER JOIN author a ON a.pnum = p.panum
GROUP BY p.panum
        ,p.title
HAVING COUNT(*) >= 3

Upvotes: 1

Sergio Rivas
Sergio Rivas

Reputation: 563

You have to specify better your fields and tables in the question, but in general you have to do a group by author and set a condition with having count. Something like 

SELECT * FROM paper JOIN author ON author ....
GROUP BY author
HAVING COUNT(*) > 3

Upvotes: 0

Related Questions