SQL query is only returning one entry, when it should be returning several

I am creating a book tagging system and I am trying to call all the books with the same tag. My query is only turning up the first book, and not any of the following books with the same tag.

Here is the books table for the database

    drop table if exists books;

      create table books (
      isbn_13 varchar (13) primary key,
      title varchar (100),
      author varchar (80),
      publish_date date,
      price decimal (6,2),
      content bytea
    );

Here is the book_tags table for the database:

    CREATE TABLE book_tags
    (
        isbn_13 character varying(13) NOT NULL,
        tag_names character varying(100) NOT NULL,
        CONSTRAINT book_tags_pkey PRIMARY KEY (isbn_13, tag_names),
        CONSTRAINT book_tags_isbn_13_fkey FOREIGN KEY (isbn_13)
            REFERENCES public.books (isbn_13) MATCH SIMPLE
            ON UPDATE NO ACTION
            ON DELETE NO ACTION
    )

Here is the BookTagDAOImpl file which calls to the query:

public List<Book> getBooksByTag(String tag) {
    List<Book> books = new ArrayList<>();
    
    try {
        connection = DAOUtilities.getConnection();
        String sql = "SELECT * FROM books INNER JOIN BOOK_TAGS ON tag_names=?";
        stmt = connection.prepareStatement(sql);
        
        stmt.setString(1, tag);
        
        ResultSet rs = stmt.executeQuery();
        
        if (rs.next()) {
            Book book = new Book();
            
            book.setIsbn13(rs.getString("isbn_13"));
            book.setAuthor(rs.getString("author"));
            book.setTitle(rs.getString("title"));
            book.setPublishDate(rs.getDate("publish_date").toLocalDate());
            book.setPrice(rs.getDouble("price"));
            book.setContent(rs.getBytes("content"));    
            
            books.add(book);
        }
        System.out.println("seeing if it will pull multiple books from one tag" + books);
        
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeResources();
    }
    
    return books;
}

Answer

As a starter: the if statement should probably be a while loop instead.

However, this is also a problem with your query:

SELECT * FROM books INNER JOIN BOOK_TAGS ON tag_names=?

This is missing a join condition between the two tables, so this basically returns all books as long as at least one of them has the searched tag. Presumably, you want:

SELECT * 
FROM books b
INNER JOIN BOOK_TAGS bt ON bt.isbn_13 = b.isbn_13
WHERE bt.tag_names = ?

You might as well use EXISTS:

SELECT b.*
FROM books b
WHERE EXISTS (
    SELECT 1
    FROM book_tags bt
    WHERE bt.isbn_13 = b.isbn_13 AND bt.tag_names = ?
)

Advertisement

Answer