CODEWITHSUNDEEP
mysql
Makerimages
Makerimages

Reputation: 384

Mysql Display titles of books from authors who have more than 1 book

Ok, I have a table of Books, Each book has 2 fileds that are revelant now: Title & Author, How can I display the titles of the books from authors who have more than 1 book? I've tried

SELECT Title, Author, COUNT(Author) AS Books FROM BOOK 
GROUP BY Author HAVING COUNT(Author) >= 2;

but this only gives me 1 title per author, the author and the amt of books this author has. e.g

The Sun Also Rises | Hemingway | 2
Foundation         | Asimov    | 2

and so forth.

How to solve? Subquerys can aslo be used

Upvotes: 1

Views: 2973

Answers (3)

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Use group_concat like that :-

SELECT group_concat(Title) as Title, Author, COUNT(Author) AS Books 
FROM BOOK GROUP BY Author HAVING COUNT(Author) >= 2;

Run this query

Upvotes: 3

flowit
flowit

Reputation: 1442

SELECT b.title,b.author,c.cnt FROM books AS b INNER JOIN
(SELECT author,COUNT(*) AS cnt FROM books GROUP BY author) AS c
ON b.author = c.author
WHERE c.cnt > 1;

Works on any database system, not just mysql.

Upvotes: 0

Shadow
Shadow

Reputation: 34231

You can determine in a subquery the authors, who have at least 2 books, then get the list of books for them:

SELECT book.Title, book.Author
FROM book
INNER JOIN (SELECT Author FROM BOOK GROUP BY Author HAVING COUNT(Author) >= 2) t
ON book.Author=t.Author;

Upvotes: 1

Related Questions