SR wrote: > As a starter project for learning Python/PostgreSQL, I am building a > Books database that stores information on the books on my bookshelf. > > Say I have three tables. > > Table "books" contains rows for book_id, title, subtitle, ISBN. > > Table "authors" contains rows for author_id, author surname, author > first names, biographical notes. > > Table "bookauthors" contains two rows: book_id, author_id. > > The bookauthors table links the books and authors tables. > > Scenario: I have a python script which creates web page listing all > books in the database, and all authors for each book. My python script > does essentially three things: > > 1. retrieve a list of all book_ids and book_titles. > > 2. for each book_id, query the bookauthors table and retrieve all > author names for that book_id. > > 3. display it all out as an html table on a web page. > > The script works fine, if a little slow. I think that's because if I > have 50 books in my database, my script performs 51 database queries (1 > for all book names; then 1 for each book). A colleague of mine > suggested that I could get away with two queries, 1 to read the book > ids and titles, and 1 to read the bookauthors table to pull in *all* > relations, and then do all the work in Python. > > I think I know where he's coming from, but I don't know where to begin. > Any clues? Is there a specific name for this technique?
The specific name you are looking for is to 'join' tables. There will be many references and tutorials available, but I suggest you start with the PostgreSQL tutorial, which is part of the documentation supplied with PostgreSQL. Here is a link to the 'join' command in the online manual. http://www.postgresql.org/docs/8.1/interactive/tutorial-join.html HTH Frank Millman -- http://mail.python.org/mailman/listinfo/python-list