CODEWITHSUNDEEP
sqljoin
DucCuong
DucCuong

Reputation: 658

Join with GROUP BY and HAVING in SQL

Supposed that I have a schema like this one. enter image description here

And now I want to retreive name, address and number of books checkout of the borrowers who have at least five book checkout.

After a while, I came up with this solution as I'm fresher in Database:

SELECT BR.Name, BR.Address,
   Checkout.NoOfBookCheckedOut
FROM Borrower BR, (
    SELECT CardNo, COUNT(BookId) AS 'NoOfBookCheckedOut'
    FROM Book_Loans
    WHERE DateOut IS NOT NULL
    GROUP BY CardNo
    HAVING COUNT(BookId) >= 5
 ) AS 'Checkout'
WHERE Checkout.CardNo = BR.CardNo

Is my solution correct? Is there any better way to optimize this query?

Upvotes: 2

Views: 361

Answers (3)

Rashmi Pandit
Rashmi Pandit

Reputation: 23838

To optimize your query, make sure you have proper indexes in place on columns used in join and where clauses.

Upvotes: 0

timo.rieber
timo.rieber

Reputation: 3867

I think you could simplify your query by joining both tables without the need for the derived table.

SELECT
    BR.Name,
    BR.Address,
    COUNT(BL.BookId) as NoOfBookCheckedOut

FROM
    Borrower BR JOIN Book_Loans BL ON BR.CardNo = BL.CardNo

WHERE
    BL.DateOut IS NOT NULL

GROUP BY
    BR.Name,
    BR.Address

HAVING
    COUNT(BL.BookId) >= 5

Upvotes: 1

Umesh
Umesh

Reputation: 2732

You can use inner join here

SELECT BR.Name, BR.Address,
   Checkout.NoOfBookCheckedOut
FROM Borrower BR
INNER JOIN (
    SELECT CardNo, COUNT(BookId) AS 'NoOfBookCheckedOut'
    FROM Book_Loans
    WHERE DateOut IS NOT NULL
    GROUP BY CardNo
    HAVING COUNT(BookId) >= 5
 ) Checkout
ON Checkout.CardNo = BR.CardNo

Upvotes: 2

Related Questions