SQL Query Using Data from 2 tables

Question

I have 2 tables: authors and books.

in authors i have attributes authorID, authorName, and authorDOB.
authorID is the primary key in this table.

in the books table i have attributes bookISBN, authorID, etc.
with bookISBN as the primary and authorID as the foreign key

i am trying to perform a query where given an author name, perform a count of all the books by that author.

here is what i got:

SET @ID =
AuthorID
FROM authors
WHERE ('Mark Twain' = AuthorName);

SELECT COUNT(*)
FROM books
WHERE (AuthorID = ID);

Any help would be greatly appreciated

Answer 1

Try:

SELECT a.authorId, a.authorName, count(*)
FROM authors a
INNER JOIN books b ON b.AuthorID=a.AuthorID
WHERE ('Mark Twain' = a.AuthorName)
GROUP BY a.authorId, a.authorName

Answer 2

i am trying to perform a query where given an author name, perform a count of all the books by that author.

Try

select count(1) 
from books b
inner join authors a on a.AuthorID=b.AuthorID
where a.AuthorName='Mark Twain'

Answer 3

You can use a function as well if you think you'd be doing the search more frequently. Just an idea.

go
create function totalbooksbyauthor (@authorname varchar(20) ) returns table
as return

select a.authorid, a.authorname, COUNT(b.bookname) bookcount
from authors a
inner join books b 
on a.authorID = b.authorID
where a.authorname = @authorname
group by a.authorid, a.authorname

go

select * from totalbooksbyauthor ('Mark Twain')