[plug] query design challenge for OOo mailmerge

[J Michael Gilks]

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