SQL : get one particular book owner

Question

I have a Student table with columns like this:

| email (PK) | name |

I have a book table with columns as such:

| bookid(PK) | title |

I have a copy table which have copies of books people own

| emailofOwner(FK to student.email) | bookid(FK to book.bookid) |

A student can of course own multiple books. My aim is to find names of students who own only 1 such book and nothing else BUT with a bookid = 3;

My attempt to get people who own only 1 book.

select c.emailofOwner
from copy c
group by c.emailofOwner
having count(*) = 1 ;

Answer 1

SELECT t1.name
FROM student t1
INNER JOIN
(
    SELECT emailofOwner
    FROM copy
    GROUP BY emailofOwner
    HAVING COUNT(DISTINCT bookid) = 1 AND MAX(bookid) = 3
) t2
    ON t1.email = t2.emailofOwner

The above query uses a subquery to restrict to students who own one and only one book whose ID is 3. The subquery is identical to your attempt except that it adds the restriction that the max book ID is 3. In this case, since there will only be one book per retained group, this is simply checking the value of the book ID.