Re: Many-to-Many relation

On Tue, May 27, 2003 at 13:03:00 -0700,
"Mukta A. Telang" <mukta(at)darya(dot)nio(dot)org> wrote:
>
> SELECT author_paper.paper_id, paper.title, author.name FROM author_paper
> full join author on (author_paper.author_id = author.author_id) full join
> paper on (author_paper.paper_id = paper.paper_id) ORDER BY
> paper.paper_id, author_paper.level
>
> I get the paper_id,title and an author of a paper.
> In this way for every author of the paper I get the records in above
> format.
>
> but what I want is paper_id, title and all the authors of the paper sorted
> according to their level..
>
> How to do this?

This really does give you the information you want. As your reading
rows you can skip the paper information as long as the paper_id doesn't
change.

If you really want the authors returned as part of the same row, you
are probably best off simulating that in your application. Without
a bound on the number of authors for a paper, you are going to need
to do separate queries for each paper which will be slower than sending
extra data to the application (at least in the normal case). You might
be able to use something from contrib/tablefunc to do what you want
if you have a bound on the number of authors per paper.