How to display a list of books that all library subscribers have read?

Question

SQL

It is necessary to display a list of books that all subscribers have read

. That is, from the lend table, you need to get such book_code values ​​\u200b\u200bthat all subscribers took (bilet_number) - so that this table contains records that each bilet_number took this particular book_code
Here is how to do it, I can’t understand in any way

It won’t work, because 1 subscriber could take the book several times

SELECT book_code FROM lend
GROUP BY book_code
HAVING COUNT(book_code) = (SELECT COUNT(bilet_number) FROM abonents)

Answer 1

You should use the distinct predicate along with the count function :

select book_code, count(distinct bilet_number) lends
from lends
group by book_code
having count(distinct bilet_number) = (select count(distinct bilet_number) from abonents)

Check SQL query