pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Darren Greer <dgreer@websightsolutions.com>
To: pgsql-sql@postgreSQL.org
Subject: Trouble with massive select statement.
Date: Mon, 21 Jun 1999 15:45:38 -0500
Message-ID: <99062116013106.13060@george> (raw)

Hello all, first a bit of information.

I have the following tables:
listeners (username text, first_name text, last_name text, email text, station
char(10) );  
listeners_data (username text, signup_date, zip_text text); 
testmaster (username text, test_date date, station char(10) );

Now what I need to do is select all the users who have taken a test (for a
particular station) within, say the last so many number of days.  The following
select statement does that.  However I need the following select statement to
work inside of another select statement.  

select distinct username, station 
	from testmaster t1 
	where 1 < (
		select count(t2.username) 
			from testmaster t2
			where t2.test_date > '05-14-1999' 
			and t2.station = 'WZZZ' 
			and t1.username = t2.username
		)
;

I already have this select statement:
	select first_name, email from listeners l, listeners_data ld where l.username = ld.username a
nd l.station = 'XXXX';
Now this select statement must only grab data if l.username exists in the data
retrieved from the first select statement I listed.  This seems awefully
convoluded to me, and I expect on a large table, this could take a long time. 
Therefore, I am open to any thoughts on a better way to do this.  Please let me
know if you need any more information and I will be glad to provide it.

Darren







view thread (5+ messages)  latest in thread

Message-ID: <99062116013106.13060@george>
Permalink:  ../99062116013106.13060@george/
Also on:    postgresql.org/message-id/99062116013106.13060@george

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: dgreer@websightsolutions.com
  Subject: Re: Trouble with massive select statement.
  In-Reply-To: <99062116013106.13060@george>
  References: <99062116013106.13060@george>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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