Re: Many-to-Many relation

On Mon, 26 May 2003, Peter Childs wrote:

> On Mon, 26 May 2003, Mukta Telang wrote:
>
> > Hi,
> >
> > I am designing a database of paper publications.
> >
> > A paper has one or more authors and an author has one or more papers.
> > In this way there is many-to-many relation.
> >
> > An author of a paper has a "level", that is an author of a paper of
> > level one is the main author of the paper and the author
> > with level two is the second author of the paper and so on.
> >
> > So we have following tables:
> > 1. author
> > 2. paper
> > 3. author_paper
> >
> > The attributes of author are:
> > 1. author_id
> > 2. name
> >
> > The attributes of paper are:
> > 1. paper_id
> > 2. journal
> > 3. year
> > 4. volume
> > 5. issue
> >
> > The attributes of author_paper are:
> > 1. author_id
> > 2. paper_id
> > 3. level
>
> SELECT * 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 think is what you want, You will need to replace the * with the fields
> you want of course! (Bung "CREATE VIEW <name> AS" to create the view)
> If you don't want to list authors without papers or papers with no
> authors you will need to change "full join" to "left join" and you may
> need to swap the order the joins occur in if you want one but not the
> other.
>
> Peter Childs
>
> >
> > Now I want to create a view which displays all the information about
> > every paper.That is title,year,journal,volume, issue and all the authors
> > of the paper sorted according to their level.
> >
> > How to do this?
> >
> > Thanks in advance,
> >
> > Mukta

I am not good in sql..and will have to brushup on joins.
Please excuse me if I am asking something silly!

suppose if I give:

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?

Mukta