pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Childs <blue.dragon@blueyonder.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Many-to-Many relation
Date: Mon, 26 May 2003 14:55:36 +0100 (BST)
Message-ID: <Pine.LNX.4.44.0305261445210.6871-100000@RedDragon.Childs> (raw)
In-Reply-To: <3ED20C8D.6EE46DFF@darya.nio.org>

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
> 




view thread (4+ messages)  latest in thread

Message-ID: <Pine.LNX.4.44.0305261445210.6871-100000@RedDragon.Childs>
Permalink:  ../Pine.LNX.4.44.0305261445210.6871-100000@RedDragon.Childs/
Also on:    postgresql.org/message-id/Pine.LNX.4.44.0305261445210.6871-100000@RedDragon.Childs

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: blue.dragon@blueyonder.co.uk
  Subject: Re: Many-to-Many relation
  In-Reply-To: <Pine.LNX.4.44.0305261445210.6871-100000@RedDragon.Childs>
  References: <3ED20C8D.6EE46DFF@darya.nio.org> <Pine.LNX.4.44.0305261445210.6871-100000@RedDragon.Childs>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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