Please help in queries

Hi,

I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.

In Book table, BookID, BookTitle, CategoryID

In Category table, CategoryID, CategoryName

In Subject table, SubjectID, SubjectName, CategoryID

In UserDownload table, UserID, BookID

In User table, UserID, UserName

I used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.

When user downloads book, I update UserDownload table.

The result I want to get is, Top Ten Download Subject. How can I get? Please help me.

The way I approach this kind of query is to first list the columns I need to return. Then I start thinking about which tables I need to touch to get that data, and then I think about how the data is related. Something like this should work for you:

SELECT
Subject.SubjectID,
Subject.SubjectName,
TopDownloads.BookCount
FROM
Subject
INNER JOIN
Book ON Subject.CategoryID = Book.CategoryID
INNER JOIN
(SELECT TOP 10 BookID, COUNT(*) AS BookCount FROM UserDownload GROUP BY BookID ORDER BY COUNT(*) DESC) AS TopDownloads ON Book.BookID = TopDownloads.BookID