From: | "Mukta A(dot) Telang" <mukta(at)darya(dot)nio(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Many-to-Many relation |
Date: | 2003-05-27 20:03:00 |
Message-ID: | Pine.SGI.3.94.1030527124239.13353A-100000@darya.nio.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Chadwick Rolfs | 2003-05-27 20:29:16 | Re: [SQL] faster output from php and postgres |
Previous Message | Jean-Luc Lachance | 2003-05-27 19:35:39 | Re: [SQL] faster output from php and postgres |