pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMany-to-Many relation
4+ messages / 4 participants
[nested] [flat]
* Many-to-Many relation
@ 2003-05-26 12:46 Mukta Telang <mukta@darya.nio.org>
2003-05-26 13:55 ` Re: Many-to-Many relation Peter Childs <blue.dragon@blueyonder.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Mukta Telang @ 2003-05-26 12:46 UTC (permalink / raw)
To: pgsql-sql
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
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Many-to-Many relation
2003-05-26 12:46 Many-to-Many relation Mukta Telang <mukta@darya.nio.org>
@ 2003-05-26 13:55 ` Peter Childs <blue.dragon@blueyonder.co.uk>
2003-05-27 20:03 ` Re: Many-to-Many relation Mukta A. Telang <mukta@darya.nio.org>
0 siblings, 1 reply; 4+ messages in thread
From: Peter Childs @ 2003-05-26 13:55 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Many-to-Many relation
2003-05-26 12:46 Many-to-Many relation Mukta Telang <mukta@darya.nio.org>
2003-05-26 13:55 ` Re: Many-to-Many relation Peter Childs <blue.dragon@blueyonder.co.uk>
@ 2003-05-27 20:03 ` Mukta A. Telang <mukta@darya.nio.org>
2003-05-27 13:07 ` Re: Many-to-Many relation Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 4+ messages in thread
From: Mukta A. Telang @ 2003-05-27 20:03 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Many-to-Many relation
2003-05-26 12:46 Many-to-Many relation Mukta Telang <mukta@darya.nio.org>
2003-05-26 13:55 ` Re: Many-to-Many relation Peter Childs <blue.dragon@blueyonder.co.uk>
2003-05-27 20:03 ` Re: Many-to-Many relation Mukta A. Telang <mukta@darya.nio.org>
@ 2003-05-27 13:07 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2003-05-27 13:07 UTC (permalink / raw)
To: Mukta A. Telang <mukta@darya.nio.org>; +Cc: pgsql-sql
On Tue, May 27, 2003 at 13:03:00 -0700,
"Mukta A. Telang" <mukta@darya.nio.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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-05-27 20:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-26 12:46 Many-to-Many relation Mukta Telang <mukta@darya.nio.org>
2003-05-26 13:55 ` Peter Childs <blue.dragon@blueyonder.co.uk>
2003-05-27 20:03 ` Mukta A. Telang <mukta@darya.nio.org>
2003-05-27 13:07 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox