Hi,

PenWin wrote:

> 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"
>

.....

>
> 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).

Once a book is published, the authors do not usually change? The data
should only need to be built once, when the book is added. Whilst not
BCNF, or even strictly cricket, it certainly should be both easier and
faster for reading.

regards
ian