X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DFAE5474E4F for ; Fri, 9 May 2003 12:48:04 -0400 (EDT) Received: from email05.aon.at (WARSL401PIP4.highway.telekom.at [195.3.96.79]) by postgresql.org (Postfix) with SMTP id 111B04758C9 for ; Fri, 9 May 2003 12:47:26 -0400 (EDT) Received: (qmail 537688 invoked from network); 9 May 2003 16:47:30 -0000 Received: from m169p013.dipool.highway.telekom.at (HELO cantor) ([62.46.11.13]) (envelope-sender ) by qmail5rs.highway.telekom.at (qmail-ldap-1.03) with SMTP for ; 9 May 2003 16:47:30 -0000 From: Manfred Koizar To: Alex Rice Cc: pgsql-sql@postgresql.org Subject: Re: does this require a stored procedure? Date: Fri, 09 May 2003 18:47:22 +0200 Message-ID: References: In-Reply-To: X-Mailer: Forte Agent 1.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-30.9 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,NO_DNS_FOR_FROM, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_FORTE autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/186 X-Sequence-Number: 13351 On Thu, 8 May 2003 12:50:02 -0600, Alex Rice wrote: >Is it possible to rewrite this SQL query so it returns one row having >the columns title and contentType, instead of two rows with the sname >column? [...] > ># SELECT rec_id, url, sname, sval FROM url, urlinfo >WHERE url.rec_id = 1821 >AND url.rec_id = urlinfo.url_id >AND sname in('title','Content-Type'); SELECT u.rec_id, u.url, t.sval AS title, c.sval AS "contentType" FROM url u INNER JOIN urlinfo t ON (u.rec_id = t.url_id AND t.sname = 'title') INNER JOIN urlinfo c ON (u.rec_id = c.url_id AND c.sname = 'Content-Type') WHERE url.rec_id = 1821; Use LEFT JOINs if it's not sure that there is always a 'title' and a 'Content-Type'. Servus Manfred