duplicates in a join

Question

the following SQL is returning every BT.Bt_Name where L.date_back is Null. I only wish to select the BT.Bt_Names where L.Bc_id is duplicated

SELECT BT.Bt_Name
FROM Book_Title BT INNER JOIN (
    Book_Copy BC INNER JOIN Loan L ON BC.Bc_id = L.Bc_id
)   ON BT.Bt_id = BC.Bt_id
WHERE L.Date_back Is NULL
GROUP BY BT.Bt_name
HAVING COUNT(L.Bc_id) >1;

Is it the joins which are causing COUNT(L.Bc_id) to be >1 for all records where L.Date_back Is NULL? FYI only one should be returned (deliberate input error).

Answer 1

Yes, you are inadvertently creating a Cartesian product between Book_Title and Loan.

Try this:

HAVING COUNT(DISTINCT BC.Bt_id) > 1

Answer 2

Why bother with the brackets? Why not this?

SELECT BT.Bt_Name, count(*) ct FROM Book_Title BT

INNER JOIN  Book_Copy BC on BT.Bt_id = BC.Bt_id

INNER JOIN Loan L ON BC.Bc_id = L.Bc_id

WHERE L.Date_back Is NULL

GROUP BY BT.Bt_name HAVING COUNT(*) >1;

Or do I have the wrong end of the stick?