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 90C00923407 for ; Tue, 27 May 2003 09:17:42 -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 55880-05 for ; Tue, 27 May 2003 09:17:11 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by developer.postgresql.org (Postfix) with SMTP id AE0499232CA for ; Tue, 27 May 2003 09:05:45 -0400 (EDT) Received: (qmail 26206 invoked by uid 500); 27 May 2003 13:07:42 -0000 Date: Tue, 27 May 2003 08:07:42 -0500 From: Bruno Wolff III To: "Mukta A. Telang" Cc: pgsql-sql@postgresql.org Subject: Re: Many-to-Many relation Message-ID: <20030527130742.GA26084@wolff.to> Mail-Followup-To: "Mukta A. Telang" , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/505 X-Sequence-Number: 13670 On Tue, May 27, 2003 at 13:03:00 -0700, "Mukta A. Telang" 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.