pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddoes this require a stored procedure?
4+ messages / 3 participants
[nested] [flat]
* does this require a stored procedure?
@ 2003-05-08 18:50 Alex Rice <alrice@ARCplanning.com>
0 siblings, 2 replies; 4+ messages in thread
From: Alex Rice @ 2003-05-08 18:50 UTC (permalink / raw)
To: pgsql-sql
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? I could write a stored procedure to lookup the title or
content-type based on the rec_id, but I would rather do it in a single
query if it's possible.
# 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: does this require a stored procedure?
@ 2003-05-08 19:26 chester c young <chestercyoung@yahoo.com>
parent: Alex Rice <alrice@ARCplanning.com>
1 sibling, 1 reply; 4+ messages in thread
From: chester c young @ 2003-05-08 19:26 UTC (permalink / raw)
To: Alex Rice <alrice@ARCplanning.com>; +Cc: pgsql-sql
--- 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');
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: does this require a stored procedure?
@ 2003-05-08 19:44 Alex Rice <alrice@ARCplanning.com>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Alex Rice @ 2003-05-08 19:44 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
On Thursday, May 8, 2003, at 01:26 PM, chester c young wrote:
> 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 );
>
Chester. Thanks, this helpful. However I think a stored procedure might
be the way to go. See the url string exists only in the url table.
Also the primary key is named url_id or rec_id depending which table,
so the using(url_id) wouldn't work for that join.
mnogosearch=> \d url
Table "public.url"
Column | Type | Modifiers
-----------------+------------------
+-------------------------------------------
----
rec_id | integer | not null default
nextval('next_url_id'::te
xt)
status | integer | not null default 0
docsize | integer | not null default 0
next_index_time | integer | not null
last_mod_time | integer |
referrer | integer | not null default 0
hops | integer | not null default 0
crc32 | integer | not null default 0
seed | integer | not null default 0
bad_since_time | integer |
site_id | integer |
server_id | integer |
pop_rank | double precision | not null default 0
url | text | not null
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)
Alex Rice, Software Developer
Architectural Research Consultants, Inc.
alrice@ARCplanning.com
alrice@swcp.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: does this require a stored procedure?
@ 2003-05-09 16:47 Manfred Koizar <mkoi-pg@aon.at>
parent: Alex Rice <alrice@ARCplanning.com>
1 sibling, 0 replies; 4+ messages in thread
From: Manfred Koizar @ 2003-05-09 16:47 UTC (permalink / raw)
To: Alex Rice <alrice@ARCplanning.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-05-09 16:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-08 18:50 does this require a stored procedure? Alex Rice <alrice@ARCplanning.com>
2003-05-08 19:26 ` chester c young <chestercyoung@yahoo.com>
2003-05-08 19:44 ` Alex Rice <alrice@ARCplanning.com>
2003-05-09 16:47 ` Manfred Koizar <mkoi-pg@aon.at>
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