how do i take the results of my subqueries to join with another table?

Question

. trying to link together the results from borrower,book, to AUTHOR.

desired results:

AUTHORID         AUTHORFIRSTNAME      AUTHORLASTNAME
1                     JIM                   SPARKS
2                     JAMES                 ALLEN
3                     MARCUS                RASHFORD
20                    PAUL                  POGBA
22                    THIERRY               HENRY

but am unsure how to link the returned, top authorids to retrieve the authorfirstname and lastname but i have not mentioned author table in the subquery

Answer 1

You can join those three tables along with rank() analytic function in the descending order for count within a subquery and then take less than equal to five in the main query :

SELECT authorid, authorfirstname, authorlastname
  FROM
  (
  SELECT a.authorid, a.authorfirstname, a.authorlastname, 
         rank() over (order by count(*) desc)  as rnk
    FROM AUTHOR a
    LEFT JOIN BOOK bk ON a.authorid = bk.authorid
    LEFT JOIN BORROWER br ON br.bookid = bk.bookid
   WHERE br.borrowdate between date'2017-01-01' and date'2017-12-31'
   GROUP BY a.authorid, a.authorfirstname, a.authorlastname
   )
  WHERE rnk <= 5
  ORDER BY rnk

If you're using DB version 12c+, it's easier to fetch them :

SELECT a.authorid, a.authorfirstname, a.authorlastname, 
       rank() over (order by count(*) desc)  as rnk
  FROM AUTHOR a
  LEFT JOIN BOOK bk ON a.authorid = bk.authorid
  LEFT JOIN BORROWER br ON br.bookid = bk.bookid
 WHERE br.borrowdate between date'2017-01-01' and date'2017-12-31'
 GROUP BY a.authorid, a.authorfirstname, a.authorlastname
 ORDER BY rnk 
 FETCH FIRST 5 ROWS WITH TIES

where I used br.borrowdate between date'2017-01-01' and date'2017-12-31' instead of to_char(br.borrowdate) like '%2017' to be able to benefit the index on the column borrowdate if exists any.

Those queries above return the rows with ties, e.g. they bring more rows than five provided that multiple rows match the value of 5th row.

Do not use rownum pseudocolumn for ranking purposes, since the value of it is calculated before ordering and might yield wrong results.