X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (developer.postgresql.org [64.117.224.193])
	by developer.postgresql.org (Postfix) with ESMTP id C656492524E
	for <pgsql-sql@postgresql.org>; Mon, 26 May 2003 03:30:36 -0400 (EDT)
Received: from developer.postgresql.org ([64.117.224.193])
	by localhost (developer.postgresql.org [64.117.224.193:10024])
	(amavisd-new) with ESMTP id 95483-04 for <pgsql-sql@postgresql.org>;
	Mon, 26 May 2003 03:30:08 -0400 (EDT)
Received: from gw1.nio.org (fw1.nio.org [210.212.160.229])
	by developer.postgresql.org (Postfix) with ESMTP id 90009925235
	for <pgsql-sql@postgresql.org>; Mon, 26 May 2003 03:30:04 -0400 (EDT)
Received: from csnio.ren.nic.in (IDENT:root@localhost.localdomain [127.0.0.1])
	by gw1.nio.org (8.11.2/8.8.7) with ESMTP id h4Q7Gas11926
	for <pgsql-sql@postgresql.org>; Mon, 26 May 2003 12:46:36 +0530
Received: from 172.27.1.111 (172.27.1.111) by ASSP-nospam ;
	26 May 03 07:16:36 -0000
Received: from darya.nio.org ([192.168.1.111])
	by csnio.ren.nic.in (8.9.3/8.8.7) with SMTP id NAA02411
	for <@csnio.ren.nic.in:pgsql-sql@postgresql.org>;
	Mon, 26 May 2003 13:24:18 +0530
Received: from darya.nio.org (himalaya.nio.org [172.27.9.1]) by darya.nio.org
	(950413.SGI.8.6.12/950213.SGI.AUTOCF) via ESMTP id OAA03538 for
	<pgsql-sql@postgresql.org>; Mon, 26 May 2003 14:02:50 -0700
Message-ID: <3ED20C8D.6EE46DFF@darya.nio.org>
Date: Mon, 26 May 2003 12:46:05 +0000
From: Mukta Telang <mukta@darya.nio.org>
X-Mailer: Mozilla 4.51 [en] (X11; I; SunOS 5.7 sun4u)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Many-to-Many relation 
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Assp-Spam-Prob: 0.00000
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200305/494
X-Sequence-Number: 13659

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