Nested SELECT query in postgresql

Question

I have three tables:

1) book

Column   Type                           | book_id | isbn | title | year |
book_id  integer Auto Increment PK      |    1    | 1234 | Book1 | 1999 |
isbn     character varying(30) UNIQUE   |    2    | 1235 | Book2 | 1999 |
title    character varying(150)         |    3    | 1236 | Book3 | 2009 |
year     smallint

2) author

Column      Type                        | author_id |    name     |
author_id   integer Auto Increment  PK  |    5      |  Joe Doe    |
name        character varying(100)      |    7      |  John Curry |
                                        |    9      |  Jim Webb   |
                                        |   11      |  Carl Lee   |

3) book_author

Column     Type                                 | book_id | author_id |
book_id    integer REFERENCES book(book_id)     |    1    |     5     |
author_id  integer REFERENCES author(author_id) |    1    |     7     |
PRIMARY KEY (book_id, author_id)                |    2    |     9     |
                                                |    3    |    11     |

A book can have one or more authors so I decided to create the book_author table to connect the book and the author tables together. I want to create a query to select for example: the book title and author name(s) of all the books that have written in 1999. This query should return something like:

title name
Book1 Joe Doe
Book1 John Curry
Book2 Jim Webb

I have manage to create two queries that somehow achieve this purpose (well not quite).

SELECT name from author WHERE author_id = 
ANY (SELECT author_id FROM book_author WHERE book_author.book_id = 1)

SELECT title FROM book WHERE book_id = ANY 
(SELECT book_id FROM book_author WHERE author_id = 5 OR author_id = 7)

Can you provide me with a single query which achieves the above mention purpose. It would be nice if you explain the structure of the query, the reasoning behind it. Thank you in advance

IMParasharG · Accepted Answer · 2019-03-18 15:20:15Z

1

You can readout Joins Between Tables

Select b.title, a.name from book_author ba 
    join author a on (a.author_id =ba.author_id) 
    join book b on (ba.book_id = b.book_id)
    where b.year=1999

Query Result :

title  name 
Book1  Joe Doe 
Book1  John Curry 
Book2 Jim Webb

answered Mar 18, 2019 at 15:20

IMParasharG

1,9051 gold badge15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AmilaMGunawardana · Accepted Answer · 2019-03-18 14:59:54Z

0

Join the tables and you can select the book name and the author choose the book_author this is the source table which has the transaction data and join with the lookup tables (book and author) to get the information about those transactions.

 Select b.title,a.name from book_author as ba 
 inner join author as a on ba.author_id=a.author_id
 Inner join book as b on b.book_id=ba.book_id

answered Mar 18, 2019 at 14:59

AmilaMGunawardana

1,8752 gold badges16 silver badges34 bronze badges

Collectives™ on Stack Overflow

Nested SELECT query in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related