CODEWITHSUNDEEP
phpmysql
dark_illusion_909099
dark_illusion_909099

Reputation: 1099

MYSQL - How to go about doing a join with subselect join

I am not sure how to word this so the question maybe look vague. I will try my best to describe the issue I am having.

I have a 3 tables:

LibraryRepairEvent
MemberBorrowsBook
Book

This is how LibraryRepairEvent looks like:

LibraryRepairEvent

This is how MemberBorrowsBook looks like:

MemberBorrowsBook

This is how Book looks like:

Book

What I am trying to do is out of all the events there, I want to find the organiser who raised the most donations and fetch some information in regards from the Book Table.

This is what I have so far :

SELECT *
FROM Book bk
JOIN (SELECT b.bookID, SUM(lre.donationsCollected) as maxDonations
FROM Book b
    JOIN MemberBorrowsBook mbb
            ON b.bookID = mbb.bookID
    LEFT JOIN LibraryRepairEvent lre 
            ON mbb.memberID = lre.organiserID
) a
ON bk.bookID = a.bookID
GROUP BY maxDonations
ORDER BY maxDonations DESC

However this query does not work as how it should be. Based on the screenshots I have included. It's returning the wrong information back.

Based on the example, LibraryRepairEvent shows organiserId [email protected] has the highest amount raised (tallying up the donations of 900). Now it will look at the MemberBorrowsBook table by using the join on memberID to get the bookID. So here I will have 45441 and 45651 as the bookID which then will be taken to the Book table. So now the end result should have been the details of these ID's but I am getting wrong data back.

Please ask me if unsure of anything.

Upvotes: 1

Views: 39

Answers (1)

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

I think this should do. Select your columns accordingly. I set up a rextester demo here, it would have helped us a lot if you had done so yourself. Perhaps next time :-)

select * from 
(
   select OrganiserID, sum(DonationsCollected) totalDonations from LibraryRepairEvent group by OrganiserID
) summary
join MemberBorrowsBook mbb on mbb.MemberID = summary.OrganiserID
join Book b on mbb.BookID = b.BookID
where totalDonations = (
    select max(totalDonations) from 
    (
        select sum(DonationsCollected) totalDonations from LibraryRepairEvent group by OrganiserID
    ) summary2
)
;

NOTE This statement returns the information related to all rank-1 organisers. So if there are more than one achieving the highest donation sum, all of them will be reported. If you only want one of them this statement can be simplified significantly.

Upvotes: 1

Related Questions