OBJECTIVE: Want to show books, book like count, authors, and author like count.

-- books: start
SELECT * FROM books
-- likes: get like count of books
JOIN likes ON likes.id = books.likes_id
-- merge table: join to book
JOIN authors_books ON books.id = authors_books.book_id
-- authors: join merge table
JOIN authors ON authors.id = authors_books.author_id
-- likes: get like count of authors
JOIN ????????

I'm stuck at authors likes. Instead of likes_id: I would like likes_count (see RESULTS GRID image).

I tried:

JOIN likes AS l2 ON likes.id = authors.likes_id

No luck. Shows all the correct column info at the top, but blank output.

CodePudding user response：

You have to join with likes twice, once for the book, another for the author.

Since you join with the same table, you need to give them aliases to distinguish them.

SELECT books.id, books.title, books.pages, book_likes.count AS book_likes, 
       authors.first_name, author.last_name, author_likes.count AS author_likes
FROM books
JOIN likes AS book_likes ON book_likes.id = books.likes_id
JOIN authors_books ON books.id = authors_books.book_id
JOIN authors ON authors.id = authors_books.author_id
JOIN likes AS author_likes ON author_likes.id = authors.likes_id