[plug] query design challenge for OOo mailmerge

[Sol]

Just relaying a thankyou to Michael and those that replied to me OL.

It all seems so easy - when you know how. 
:-)

Ta very much,
sol


On Thu, 13 Mar 2003 05:00 pm, J Michael Gilks wrote:
> Are you getting a cartesian join, ie all possible combinations are printed.
> I think this may be happening from looking at the query.
> Try adding a line to specify the library item, something like
>
> FROM mailing_list, loan_books, library_catalogue
> WHERE loan_books.member_id = mailing_list.id
> AND oan_books.library_item_id = ibrary_catalogue.library_item_id
> AND loan_books.date_returned IS NULL;
>
> That should give a reasonable resultset to work with.
> Good luck.
> Love
> Mike.
>
> On Thursday 13 Mar 2003 2:06 pm, Sol wrote:
> > This is really not a Linux Q, but more an SQL Q, but rather than
> > subscribe to yet another list, I thought I might ask on this list as I've
> > no doubt that most of you know better than I do. :-)
> >
> > I've set up an ODBC connection between OOo and Postgresql and all is
> > sweet. I am able to produce various reports etc, but I want to do a mail
> > merge in OpenWriter but I just can't out the query I want to do. I think
> > it requires a subselect. And I'm not even sure if it's possible...
> >
> > I'm trying to send a letter to everyone with overdue books for a small
> > library. I can write queries to get all the names and addresses; and I
> > can write queries that list all of the overdue books. BUT what I want to
> > do is write a query that fills in all the name and address details and
> > then lists the overdue books. Each member can have several books, and
> > this is where i get stuck.
> >
> > This query I wrote doesn't work, but it might give an idea what I'm
> > trying to do:
> >
> > SELECT mailing_list.id, mailing_list.lastname, mailing_list.firstname,
> > mailing_list.address, mailing_list.town_city, mailing_list.postcode,
> > mailing_list.state, mailing_list.country, loan_books.member_id,
> > loan_books.date_loaned, loan_books.library_item_id,
> > library_catalogue.library_item_id, library_catalogue.library_item_type,
> > library_catalogue.title, library_catalogue.author
> > FROM mailing_list, loan_books, library_catalogue
> > WHERE loan_books.member_id = mailing_list.id
> > AND loan_books.date_returned IS NULL;
> >
> > I've worked out that the reason that this doesn't work is because I'm
> > asking for multiple results from the loan_books and library_catalogue
> > tables for some mailing_list table results. But I don't know how to get
> > results that work something like this:
> > Firstname | Lastname | Address   | Postcode | Title   | Author   | Date
> > Loaned
> > -------------------------------------------------------------------------
> >-- --------- Sol	    Hanna	1 Thing st  6660      Book A    Gonzales
> > 2002-12-12 Book B 	Rodrigues  2003-01-10
> > 					      Book X	Partridge  2003-01-28
> >
> > I'm sure something like this can be produced by SQL even though I don't
> > know how. But will OOo understand what to do with the results?
> > ie: can I produce one letter per person which contains all of the overdue
> > books that they have? Like so:
> > --------------------------
> > <firstname> <lastname>
> > <address>
> > <town_city> <postcode>
> >
> > Dear <firstname>,
> >
> > You have these books:
> >
> > <title>	<author> <date loaned>
> >
> > Give them back or else!
> >
> > Conan the Librarian
> > ----------------------------------
> > Kind regards,
> > sol