Re: Query combination query.

Well I am very impressed, and grateful that you could solve my problem. Many thanks. (I sure wish I could come up with syntax like that on my own).

Thanks again Sean,
/j-p.

--

On Tue, 22 Feb 2005, Sean Davis wrote:

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>