RStyle RStyle - 3 months ago 9
SQL Question

Sql get one particular book owner

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.

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

Answer
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.

Comments