X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 50C5B475EE2 for ; Thu, 8 May 2003 15:26:55 -0400 (EDT) Received: from web12701.mail.yahoo.com (web12701.mail.yahoo.com [216.136.173.238]) by postgresql.org (Postfix) with SMTP id D5634474E4F for ; Thu, 8 May 2003 15:26:33 -0400 (EDT) Message-ID: <20030508192633.41386.qmail@web12701.mail.yahoo.com> Received: from [68.35.166.87] by web12701.mail.yahoo.com via HTTP; Thu, 08 May 2003 12:26:33 PDT Date: Thu, 8 May 2003 12:26:33 -0700 (PDT) From: chester c young Subject: Re: does this require a stored procedure? To: Alex Rice Cc: sql pgsql In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Spam-Status: No, hits=-24.0 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, RCVD_IN_RFCI,REPLY_WITH_QUOTES 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/179 X-Sequence-Number: 13344 --- 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'); > > rec_id | url | sname | sval > -------+-------------- > +--------------------------------------------------------- > 1821 | http://.../ | title | Architectural Research > Consultants, Incorporated Contact Information > 1821 | http://.../ | Content-Type | text/html > (2 rows) > > mnogosearch=> \d urlinfo > Table "public.urlinfo" > Column | Type | Modifiers > --------+---------+----------- > url_id | integer | not null > sname | text | not null > sval | text | not null > Indexes: urlinfo_id btree (url_id) > > Thanks, > Alex Rice, Software Developer > Architectural Research Consultants, Inc. > alrice@ARCplanning.com > alrice@swcp.com do you mean something like: select t.url_id, t.url as title_url, t.sval as title_val, c.url as content_url, c.sval as content_val from (select * from urlinfo where sname = 'title') t join (select * from urlinfo where sname = 'Content-type') c using( url_id ); __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com