Given two related tables, how to determine the most common relationships?

Question

given a 3 tables: users, books, book_users, how would I determine what are the commons books?

users: id, first_name, last_name
books: id, name
books_users: book_id, user_id

Designer Output, something like:

book | count
radBookName | 22
SemiRad | 22

Thanks

Answer 1

You seems want simple JOIN with GROUP BY clause :

SELECT b.name, count(*) as user_count
FROM books b INNER JOIN
     books_users bu
     ON bu.book_id = b.id
GROUP BY b.name;

This would produce duplicate count if one book has same user, if you want unique count then use count(distinct bu.user_id) instead.