CODEWITHSUNDEEP
sqloracle-sqldeveloperoracle-xe-18.4
Comradedrago
Comradedrago

Reputation: 25

How to filter query based on table where they dont share a field?

Ok so i've been working on this SQL statement for while and just cant figure it out. I need to be able to see the top 5 authors that clients have borrowed from in 2017. My tables look like this

1. Client - fields (clientId,clientFirstName,clientlastName,clidentDoB)
2.author - fields (AuthorID, AuthorFirstname, AuthorLastname,AuthorNation )
3. book - fields (BookId, BookAuthor,BookTitle,BookGenre)
4. borrower - fields (borrowID,BorrowDate, ClientID,BookId)

so I understand that I need to pull the names from author table, based on the number of books borrowed, I also understand that borrower.bookId is equal to book.BookId and Author.AuthorID is equal to Book.BookAuthor. I should be able to set it so that it sees books borrowed in 2017, then filters by the most popular by taking the borrowBookId and adding each instance of the same Id together and seeing what bookID matches BookAuthor in book table and then use that to compare ID to get the first and last name printed.

I have tried

SELECT author.authorfirstname,author.authorlastname 
FROM author Join ON author.authorid = book.bookauthor   
WHERE (borrower.borrowdate <='31/12/2017' AND borrower.borrowdate >= '01/01/2017');

I know this won't work but Im not sure how to get that bridge from author to borrower.

Sample data and expected output from it.

ok sure. Lets say I have 4 authors and we want the top 3. We also have only borrows in 2017 counting. The client table isn't really needed for this so lets fill in the others with some data. table field names same as in original question sample for this would be
author table

(1,bob,ross, USA) 
(2, fred, martin, USA) 
(3, alex,joe,CAN)
(4, dan, reed, can)

Book table

(1,1,bobsbook,fantasy) 
(2,1,bobagain,fantasy) 
(3,1,returnofbob,fantasy)
 (4,2,fredsadventure,fantasy) 
(5, 2, fedagain, fantasy) 
(6, 2, fedstrikes, fantasy) 
(7,3,alexjoes, fantasy)
 (8, 3, alexjoeagain,fantasy)
(9,4, dansbook, fantasy)

borrow table

(1, 20/01/2017,,1, 1)
(2, 20/01/2017,,3, 2)
(3, 20/01/2017,,2, 1)
(4, 20/01/2017,,1, 3)
(5, 20/01/2017,,6, 2)
(6, 20/01/2017,,8, 4)
(7, 20/01/2017,,4, 4)
(8, 20/01/2017,,9, 6)
(9, 20/01/2017,,2, 7)
(10, 20/01/2017,,3, 9)
(11, 20/01/2017,,4, 9)

the end result would be

AuthorFirstName  AuthorLastname 
bob               ross
Fred              Martin
Dan               Reed

This is because they had the most borrows in 2017 date range, they are in order with bob at 5, fred at 3 and dan at 2. It also only prints the top 3 people so alex joe is left off the list.

code given to be by @fahmi

SELECT author.authorfirstname,author.authorlastname 
FROM author 
Join book ON author.authorid = book.bookauthor
join borrower on book.bookid=borrower.bookid
WHERE borrower.borrowdate <='31/12/2017' AND borrower.borrowdate >= '01/01/2017'

;

this has given me a list of the authors and it lists each instance of the burrow but I need the list merged so I only see the name one and so that it is in order by most borrows and limits it self.

Upvotes: 2

Views: 49

Answers (2)

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

Use TOP-N query feature from Oracle,

select au.authorfirstname
      ,au.authorlastname 
  from author au
  join book bk
    on au.authorid = bk.bookauthor
  join borrower bw
    on bk.bookid=bw.bookid
 where extract(year from bw.borrowdate) 
       = 2017
group by au.authorfirstname
        ,au.authorlastname
order by count(bk.bookid) desc
fetch first 3 rows with ties;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You need another join to borrower with book.bookid=borrower.bookid relationship

SELECT top 3 author.authorfirstname,author.authorlastname,count(borrower.bookid) as cnt
FROM author 
Join book ON author.authorid = book.bookauthor
join borrower on book.bookid=borrower.bookid
WHERE borrower.borrowdate <='31/12/2017' AND borrower.borrowdate >= '01/01/2017'
group by author.authorfirstname,author.authorlastname
order by cnt desc

Upvotes: 0

Related Questions