CODEWITHSUNDEEP
mysqlsqldatabaseselectsubquery
Owais Ahmed
Owais Ahmed

Reputation: 1438

SQL finding most popular books

I have Books table

BookID BookName
 1       BookA
 2       BookB
 3       BookC

Member table

MemberID MemberName
 1          MemberA
 2          MemberB

Borrow Table

MemberID BookID
 1         1
 1         2
 2         1
 2         2

I want to find out five popular book by Memeber A

I tried the following query

SELECT        TOP (5) Book.BookTitle, COUNT(*) AS Count, Member_1.MemberName
FROM            Book INNER JOIN
                         Borrow ON Book.BookID = Borrow.BookID INNER JOIN
                         Member ON Borrow.MemberID = Member.MemberID INNER JOIN
                         Member AS Member_1 ON Borrow.MemberID = Member_1.MemberID
                         where Member.MemberName='A'
GROUP BY Book.BookTitle, Member_1.MemberName
ORDER BY Count DESC

But this is not giving me the actual result.

Any suggestion would be appreciated.

Upvotes: 0

Views: 900

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you have too many joins:

SELECT TOP (5) b.BookTitle, COUNT(*) AS Count, m.MemberName
FROM Book b INNER JOIN
     Borrow bo
     ON bo.BookID = b.BookID INNER JOIN
     Member m
     ON bo.MemberID = m.MemberID 
WHERE m.MemberName = 'A'
GROUP BY b.BookTitle, m.MemberName
ORDER BY Count DESC;

Note: This syntax is usually associated with SQL Server and does not work in MySQL. In MySQL, you would use LIMIT 5 rather then SELECT TOP (5).

Upvotes: 1

Related Questions