CODEWITHSUNDEEP
sqloracle-database
Sha
Sha

Reputation: 125

SQL, only returning rows where at least one more entry with the same value in one column is found

Sorry if my wording is really bad. I'm not very good with remembering the terms just yet.

I have something like the following (and I can't make any changes to it):

Books(BookID, Title)

Author(BookID, WritID)

Writer(WritID, FirstName, LastName)

I want to be able to list the books by title who have more than one author, as the authors are set out something like this:

Book01, Dude01

Book02, Dude01

Book02, Dude02

I've been trying to use COUNT and HAVING to get the duplicates and JOIN to connect Books and Author but I've got a feeling the following is completely off. :(

select Title, BookID, count(*)
from Author
join Books
on Author.BookID=Books.BookID
having count(*) >= 2;

Any help or point in the right direction would be much appreciated.

Upvotes: 1

Views: 54

Answers (2)

Meetu Singh
Meetu Singh

Reputation: 251

For aggregated functions, use Group By clause.

The SQL GROUP BY Clause can be used in a SQL SELECT query to collect data across multiple records and group the results by one or more columns.

Upvotes: 3

Szymon
Szymon

Reputation: 43023

You're missing GROUP BY - you need to group by all the columns you want to see in the results that are not aggregated:

select Books.Title, Books.BookID, count(*)
from Author
join Books
on Author.BookID=Books.BookID
group by Books.Title, Books.BookID
having count(*) >= 2;

SQL Fiddle demo

Upvotes: 2

Related Questions