RStyle RStyle - 1 year ago 80
SQL Question

SQL : get one particular book owner

I have a

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 | 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 Source
FROM student t1
    SELECT emailofOwner
    FROM copy
    GROUP BY emailofOwner
    HAVING COUNT(DISTINCT bookid) = 1 AND MAX(bookid) = 3
) t2
    ON = 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download