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

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@postgresql.org so that your
message can get through to the mailing list cleanly

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@postgresql.org so that your
> message can get through to the mailing list cleanly
>


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

I'm tyring to use the Perl:BI module to connect to a postgreSQL database but
it doesn't seem to like it?

What is the connection statement?! Have used:

use DBI;
$dbh=DBI->connect('DBI:Pg:user_name', 'user_name', 'password')

but this returns a message that the connection failed due to a missing "=" after
the user_name?

Any suggestions?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Tue, Feb 22, 2005 at 13:39:47 +0000,
SG Edwards <s0460205@sms.ed.ac.uk> wrote:
> I'm tyring to use the Perl:BI module to connect to a postgreSQL database but
> it doesn't seem to like it?
>
> What is the connection statement?! Have used:
>
> use DBI;
> $dbh=DBI->connect('DBI:Pg:user_name', 'user_name', 'password')

Perhaps you want:
$dbh=DBI->connect('DBI:Pg:dbname=user_name', 'user_name', 'password')

>
> but this returns a message that the connection failed due to a missing "=" after
> the user_name?
>
> Any suggestions?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Tue, 22 Feb 2005, SG Edwards wrote:

> I'm tyring to use the Perl:BI module to connect to a postgreSQL database but
> it doesn't seem to like it?

I'm using this with perl 5.6.1 DBI and postgreSQL 7.2.4. I forget which
DBD.


my $connect_string = 'dbi:Pg:dbname=mode_db;user=mode_user';

$dbh = DBI->connect($connect_string)
or die "Got error $DBI::errstr when connecting to database\n";

This is for a backend cron job, hence it can just die if it fails.

The password isn't needed in my case, I trust localhost users. Basically
you just have to play with it, trying different ways until it works.

brew

================================================== ========================
Strange Brew (brew@theMode.com)
Check out my Stock Option Covered Call website http://www.callpix.com
and my Musician's Online Database Exchange http://www.TheMode.com
================================================== ========================


---------------------------(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