I have a student table with rows as such:
|email(PK)| name |
I have a book table with rows 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 ofcourse 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.
from copy c
group by c.emailofOwner
having count(*) = 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.