CODEWITHSUNDEEP
mysqljoincount
Joosep
Joosep

Reputation: 13

Mysql adding join subquery to count

I've got two tables Loan and Book.

With this query I count most loaned books from Loan by counting Book_ID:

select Book_ID, count(Book_ID) as Count
from Loan
group by Book_ID
order by Count desc;

Now I would like to add Book title from Book table here. I have Book_ID as Foreign key in Loan table. I'm not quite sure how to put this query together.

This is what I tried but no results:

select Book_ID, count(Book_ID) as Count, Book.Title
from Loan, Book
where Loan.Book_ID = Book.Book_ID
group by Book_ID
order by Count desc;

Upvotes: 1

Views: 25

Answers (1)

Paul Stanley
Paul Stanley

Reputation: 4098

You have ambigious column names since you have joined to another table, try giving them an alias:

select Book.Book_ID, count(Book.Book_ID) as Count, Book.Title
from Loan, Book
where Loan.Book_ID = Book.Book_ID
group by Book.Book_ID
order by Count desc;

Upvotes: 1

Related Questions