[plug] query design challenge for OOo mailmerge

Sol sol at autonomon.net
Thu Mar 13 14:06:13 WST 2003


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



More information about the plug mailing list