Re: Query combination query.

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: john-paul delaney <jp(at)justatest(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Query combination query.
Date: 2005-02-22 11:16:50
Message-ID: 3FAE5C17-84C3-11D9-A6C7-000D933565E8@mail.nih.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Untested, but how about something like:

SELECT subject,created,topic_id,(select count(topic_seq) from
ap_form_msq as b where b.topic_id=a.topic_id) from ap_forum_msq as a
where topic_seq=1;

Sean

On Feb 22, 2005, at 6:20 AM, john-paul delaney wrote:

> Hello List... Newbie question - is it possible to combine the following
> two queries into one query statement returning, subject, created,
> topic_id, count?
>
> thanks,
> /j-p.
>
>
> (Query1)
> SELECT subject, created, topic_id FROM ap_forum_msg WHERE topic_seq =
> 1;
>
> subject | created | topic_id
> -----------------+-------------------------------+----------
> A topic test 00 | 2005-02-22 09:14:25.444209+01 | 1
> A topic test 01 | 2005-02-22 09:15:44.320408+01 | 2
>
>
>
> (Query 2)
> SELECT topic_id, count(topic_seq) FROM ap_forum_msg GROUP BY topic_id;
>
> topic_id | count
> ----------+-------
> 1 | 2
> 2 | 3
>
>
> (Table ap_forum_msg)
>
> Column | Type | Modifiers
> ----------+--------------------------+---------------
> topic_id | integer | not null
> topic_seq | integer | not null
> author_id | integer | not null
> created | timestamp with time zone | default now()
> subject | character varying(100) |
> msg_text | character varying |
> forum_id | integer | not null
> Indexes: ap_forum_msg_pkey primary key btree (topic_id,
> topic_seq)
> Foreign Key constraints: $1 FOREIGN KEY (author_id) REFERENCES
> apo_artist(apo_art_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
> $2 FOREIGN KEY (forum_id) REFERENCES
> ap_forum(forum_id) ON UPDATE NO ACTION ON DELETE CASCADE
>
>
> --
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message john-paul delaney 2005-02-22 11:20:38 Query combination query.
Previous Message Michał Maluga 2005-02-22 10:02:00 Re: Windows 8.0 Install Failure