Обсуждение: Query combination query.
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
--
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
Well I am very impressed, and grateful that you could solve my problem. Many thanks. (I sure wish I could come up withsyntax 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 >
I'm tyring to use the Perl::DBI 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?
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::DBI 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
On Tue, 22 Feb 2005, SG Edwards wrote:
> I'm tyring to use the Perl::DBI 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
==========================================================================