Rene Canter Rene Canter - 1 month ago 7
SQL Question

SQL Query needed to get information from TWO separate tables

I am trying to create a query that will list all books by the same author. Most of the list has only one book by one author, but I want the author that has multiple books listed in db to display those book for that author.

I have two tables:


  • Book -
    AuthorID, BkTitle
    , etc

  • Author -
    AuthorID, AuthFName, AuthLName



I want the result to be sorted by
AuthLName
and the report to consist of any books in db that have same
authorid
.

Example result wanted:

AUTHORID BKTITLE AUTHFNAME AUTHLNAME
--------- ----------------- ------------ -----------
504 KNIGHT FREEDOM Chris Feehan
504 KNIGHT SHOWDOWN Chris Feehan


Currently, I have the following code:

select AUTHORID, BKTITLE
from BOOK
where AUTHORID in
(select AUTHORID from
(select AUTHORID,
count(*) as BOOK_COUNT
from BOOK
group by AUTHORid
order by AUTHORid )
where BOOK_COUNT >= 2);


which gives:

AUTHORID BKTITLE
---------- --------------------
504 KNIGHT FREEDOM
504 KNIGHT SHOWDOWN


I need to find a way to get the information from the Author Table and add it in this.

Answer

This should do:

SELECT b.AUTHORID, b.BKTITLE, a.AUTHFNAME, a.AUTHLNAME
FROM BOOK b
  INNER JOIN AUTHOR a
    ON b.AUTHORID = a.AUTHORID
AND b.AUTHORID IN
(
  SELECT AUTHORID
  FROM BOOK
  GROUP BY AUTHORID
  HAVING COUNT(AUTHORID) > 1
)
ORDER BY a.AUTHLNAME, a.AUTHFNAME
Comments