Postgres - Multiple joins is causing my query to return incorrect data

Build complex SQL step by step.

This gives you the books that have both the required tags. It's only as reliable as your table definition. Your table definition shouldn't allow one book to have the same tag twice. You need a UNIQUE constraint on (book_id, tag_id).

SELECT book_id 
FROM books_tags
WHERE books_tags.tag_id IN (716, 101)
GROUP BY book_id
HAVING COUNT(tag_id) = 2

book_id
--
6
3

You can use that in a JOIN.

SELECT books.id
FROM books
INNER JOIN (
    SELECT book_id 
    FROM books_tags
    WHERE books_tags.tag_id IN (716, 101)
    GROUP BY book_id
    HAVING COUNT(tag_id) = 2) bt ON bt.book_id = books.id

book_id
--
6
3

Joining on the table of votes should drop book_id 6 from the result. (No votes for 6.)

SELECT books.id
FROM books
INNER JOIN (
    SELECT book_id 
    FROM books_tags
    WHERE books_tags.tag_id IN (716, 101)
    GROUP BY book_id
    HAVING COUNT(tag_id) = 2) bt ON bt.book_id = books.id
INNER JOIN books_votes bv ON bv.book_id = books.id

book_id
--
3

Now you can add the vote column to the query.

SELECT books.id, bv.vote
FROM books
INNER JOIN (
    SELECT book_id 
        FROM books_tags
    WHERE books_tags.tag_id IN (716, 101)
    GROUP BY book_id
    HAVING COUNT(tag_id) = 2) bt ON bt.book_id = books.id
INNER JOIN books_votes bv ON bv.book_id = books.id

book_id  vote
--
3        1

Finally, you can sum the votes.

SELECT books.id, SUM(bv.vote) AS total_votes
FROM books
INNER JOIN (
    SELECT book_id 
        FROM books_tags
    WHERE books_tags.tag_id IN (716, 101)
    GROUP BY book_id
    HAVING COUNT(tag_id) = 2) bt ON bt.book_id = books.id
INNER JOIN books_votes bv ON bv.book_id = books.id
GROUP BY books.id;

book_id  total_votes
--
3        1

Your version doesn't work, because it returns the wrong book id numbers. The combination of the JOIN on books_votes and the WHERE clause doesn't do what you expected it to do.

SELECT books.id AS books_id
FROM books
JOIN books_votes ON books.id = books_votes.book_id
JOIN books_tags ON books.id = books_tags.book_id
WHERE books_tags.tag_id IN (716, 101)
GROUP BY books.id 

books_id
--
3
2

Book 2 is included not because it has both tags, but because it has two votes.

SELECT books.id AS books_id, books_tags.tag_id, books_votes.vote
FROM books
JOIN books_votes ON books.id = books_votes.book_id
JOIN books_tags ON books.id = books_tags.book_id
WHERE books_tags.tag_id IN (716, 101)
ORDER BY books_id, tag_id

book_id  tag_id     vote
--
2        101        1
2        101        1
3        101        1
3        716        1

As I understand you need all the books that have tags 716 and 101, and you need the vote count per each book.

select *,
    (select count(*) from book_votes as vts where vts.book_id = bks.id) as vote_count
from books as bks
where 
    id in 
    (
        select book_id
        from books_tags as tgs
        where tgs.tag_id in (716, 101)
        group by book_id
        having count(*) = 2
    )

Result:

id          book_title      vote_count
----------- --------------- -----------
3           third book      1
6           sixth book      0