Postgres query returning books with only one author

Question

I'm trying to make a query returning the book title, the book summary and the author name from the books with only one author ordered by the book id. A book can have many authors and an author can write many books, so it's a many to many relationship, but I'm making something wrong or forgetting something.

My tables are:

book_tb(id, title, summary);
author_tb(id, name);
book_author_tb(id, book_id, author_id);

And I have tried:

SELECT b.title, b.summary, a.name 
FROM book_tb b
INNER JOIN book_author_tb ba ON b.id = ba.book_id
INNER JOIN author_tb a ON ba.author_id = a.id
GROUP BY b.title, b.summary, a.name, b.id
HAVING count(ba.author_id) = 1
ORDER BY b.id;

Answer 1

One way of doing this is:

SELECT b.title, b.summary, a.name 
FROM book_author_tb ba 
INNER JOIN book_tb b ON b.id = ba.book_id
INNER JOIN author_tb a ON a.id = ba.author_id
WHERE (
  SELECT COUNT(*) 
  FROM book_author_tb
  WHERE book_id = b.id
) = 1
ORDER BY b.id

This:

SELECT COUNT(*) FROM book_author_tb WHERE book_id = b.id

makes sure that the books returned have only 1 author.
Another way (maybe more efficient):

SELECT b.title, b.summary, a.name 
FROM (
  SELECT book_id, MAX(author_id) author_id 
  FROM book_author_tb
  GROUP BY book_id
  HAVING COUNT(*) = 1
) ba 
INNER JOIN book_tb b ON b.id = ba.book_id
INNER JOIN author_tb a ON a.id = ba.author_id
ORDER BY b.id

Answer 2

I would do this with just aggregation:

  SELECT b.title
        ,b.summary
        ,MAX(a.name) AS name
    FROM book_author_tb AS ba
      INNER JOIN book_tb AS b 
        ON b.id = ba.book_id
      INNER JOIN author_tb AS a 
        ON a.id = ba.author_id
    GROUP BY b.title
            ,b.summary
    HAVING count(*) = 1;

This version is counting the number of authors on each book (assuming no duplicates, which is reasonable).

Your version is also aggregating by the author. The count() is only going to be "1" in that case.