Hi!

I have a master table and a detail table. I want to select rows from the
master table in such a way that the records from a detail table will "merge"
into one record. An example will be better to describe the desired behavior:

Master table: BOOKS (BOOKID, BOOKNAME)
Detail table: AUTHORS (BOOK, AUTHORNAME)
(I know, I know, it is really a M:N relationship, but let's keep it a simple
1:N for now)

I would like to get a result set which would look something like this:

1, "Lord of the Rings", "J.R.R.Tolkien"
2, "Daughter of the Empire", "R.E.Feist|Janny Wurts"

The question is, what's the best way of doing that? I can see several:

1) SELECT * FROM BOOKS. Application performs SELECT * FROM AUTHORS WHERE
BOOK=:id for each book and merges the authors in its code.

2) SELECT * FROM BOOKS LEFT JOIN AUTHORS ON BOOK=BOOKID. Application reads a
record and then keeps adding more authors to the author variable until
bookid changes.

3) SELECT * FROM RETURN_BOOKS1. RETURN_BOOKS1 is a stored procedure which
essentially performs the task 1). It looks something like this:
FOR SELECT * FROM books INTO :bookid, :bookname DO
BEGIN
author = NULL;
FOR SELECT authorname FROM authors WHERE book=:bookid INTO :authorname DO
IF (author IS NULL) THEN author = authorname;
ELSE author = author||'|'authorname;
SUSPEND;
END

4) SELECT * FROM RETURN_BOOKS2. RETURN_BOOKS2 works much the same way the
application does in option 2.

5) Add an extra field AUTHOR_CACHE to BOOKS and keep that field updated by
UPDATE/INSERT/DELETE triggers. Then simply SELECT * FROM BOOKS.

Personally, I am inclined to use the last one - while it slows down data
modification, it is very fast for data retrieval (and I expect the retrieval
would be a far more common task than the modification). I would expect the
options 1 and 3 to be quite slow due to the large number of executed SELECT
statements, but it might perform OK if I need to do some aggregating instead
of just returning rows. Options 2 and 4 mean a rather complex application/SP
processing, but they would probably be faster than 1 and 3 because only one
SELECT would be called.

What do you think?

Pepak