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')WHEREurl.rec_id = 1821;
Use LEFT JOINs if it's not sure that there is always a 'title' and a
'Content-Type'.
ServusManfred
