From: Manfred Koizar <mkoi-pg@aon.at>
To: Alex Rice <alrice@ARCplanning.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: does this require a stored procedure?
Date: Fri, 09 May 2003 18:47:22 +0200
Message-ID: <p7mnbv4tn42is323v8ja9jd3orbrrcfcdr@4ax.com> (raw)
In-Reply-To: <E01C567E-8185-11D7-A998-000393529642@ARCplanning.com>
References: <E01C567E-8185-11D7-A998-000393529642@ARCplanning.com>
On Thu, 8 May 2003 12:50:02 -0600, Alex Rice <alrice@ARCplanning.com>
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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mkoi-pg@aon.at, alrice@ARCplanning.com
Subject: Re: does this require a stored procedure?
In-Reply-To: <p7mnbv4tn42is323v8ja9jd3orbrrcfcdr@4ax.com>
* 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