>> Say I have three tables.
>
> Only three? <G>
Well, yeah, OK, it's more than that, but after years of being worn away
by "Post a minimal example" requests on comp.text.tex, a minimal
example is what you got...
> Something like {untested... Might need to do a subselect for the
> second JOIN}:
>
> SELECT book_id, title, subtitle, ISBN, surname, firstname, notes from
> books
> LEFT OUTER JOIN bookauthors on books.book_id = bookauthors.book_id
> JOIN authors on bookauthors.author_id = authors.author_id
> ORDER BY books.book_id
>
> The reason for the LEFT OUTER JOIN, if I recall the syntax, is to
> ensure that you get any books that don't have any authors. The sort
> order is to: one) make sure the records are grouped properly for later
> processing
Thanks for the stuff on LEFT OUTER JOIN. Authorless books would be one
of those things I wouldn't have noticed going astray.
> The output will duplicate the book information for those books that
> have multiple authors (the simple meaning of "unnormalized"):
>
> 2,A Book, Of Nothing, 123, Who, Guess, something
> 2,A Book, Of Nothing, 123, Second, I'm, or other
I think this goes along with what I thought of immediately after
posting the question: one query to gather all info needed, then
post-process in Python to order it all (so *that's* why I posted
here...). My thoughts had been to turn
[ 1, "Puppetry", "Bill" ]
[ 1, "Puppetry", "Ben" ]
[ 1, "Puppetry", "Flowerpot Men" ]
into
[ 1, "Puppetry", [ "Bill", "Ben", "Flowerpot Men" ] ]
(if that's not overcomplicating it a bit)...
> To make your report, you would output the book specific information
> only when it changes (this means you need to initialize a temp record to
> null data, and compare each record to the temp; when the compare fails,
> put out the new book data, and copy it into the temp -- in this example,
> just saving the book ID number would be sufficient, as long as it is a
> unique/primary key). THEN, put out the Author information. If the
> comparison of book data passes, it is the same book with an additional
> author, you just need to output the author data.
>
> tmp_bookID = None
> for bk in theCursor:
> if tmp_bookID != bk[0]: #assumes book_id is first field
> Output_Book_Data(bk)
> tmp_bookID = bk[0]
> Output_Author_Data(bk)
... which appears to be along the lines of what your code does! (Where
Output_Author_Data(bk) could append to the author list of the current
book.
I'll go away and see how I can 'adapt' your example code.
Thanks!
Shay
--
http://mail.python.org/mailman/listinfo/python-list
