pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp on a query
8+ messages / 6 participants
[nested] [flat]
* help on a query
@ 2004-10-08 03:03 Michelle Murrain <tech@murrain.net>
0 siblings, 1 reply; 8+ messages in thread
From: Michelle Murrain @ 2004-10-08 03:03 UTC (permalink / raw)
To: pgsql-sql
Hi all,
This is one of those things I know I should know, but it's not coming to
me. It's probably really simple.
I have two related tables, registrations and receipts, related by the
field registration_id.
So registrations looks kinda like:
registration_id bigint (primary key)
foo varchar(10)
bar varchar(20)
and receipts looks like:
receipt_id bigint (primary key)
registration_id bigint (foreign key)
amount float
baz varchar(10)
If someone has paid, there is a row in the receipts table for that
registration ID#.
I need to find a list of the registration IDs that *don't* have an entry
in the receipts table.
Thanks in advance!!!
--
.Michelle
--------------------------
Michelle Murrain
mmurrain at dbdes dot com
413-222-6350 ph
617-889-0929 ph
952-674-7253 fax <--- new
Page: pageme@murrain.net
AIM:pearlbear0 ICQ:129250575
Skype: pearlbear
Jabber: pearlbear@transactim.net
"I see all the different religious traditions as paths for the
development of inner peace, which is the true foundation of world peace.
These ancient traditions come to us as a gift from our common past. Will
we continue to cherish it as a gift and hand it over to the future
generations as a legacy of our shared desire for peace?" - His Holiness
the Dalai Lama
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: help on a query
@ 2004-10-08 03:10 Thomas F.O'Connell <tfo@sitening.com>
parent: Michelle Murrain <tech@murrain.net>
0 siblings, 1 reply; 8+ messages in thread
From: Thomas F.O'Connell @ 2004-10-08 03:10 UTC (permalink / raw)
To: mmurrain@dbdes.com; +Cc: pgsql-sql
A query that should get the job done is:
SELECT registration_id
FROM registrations r
WHERE NOT EXISTS (
SELECT 1
FROM receipts
WHERE registration_id = r.registration_id
);
There might be a more efficient version with JOINs that don't require a
subquery, but this should get you started.
-tfo
On Oct 7, 2004, at 10:03 PM, Michelle Murrain wrote:
> Hi all,
>
> This is one of those things I know I should know, but it's not coming
> to me. It's probably really simple.
>
> I have two related tables, registrations and receipts, related by the
> field registration_id.
>
> So registrations looks kinda like:
>
> registration_id bigint (primary key)
> foo varchar(10)
> bar varchar(20)
>
> and receipts looks like:
>
> receipt_id bigint (primary key)
> registration_id bigint (foreign key)
> amount float
> baz varchar(10)
>
> If someone has paid, there is a row in the receipts table for that
> registration ID#.
>
> I need to find a list of the registration IDs that *don't* have an
> entry in the receipts table.
>
> Thanks in advance!!!
> --
> .Michelle
>
> --------------------------
> Michelle Murrain
> mmurrain at dbdes dot com
> 413-222-6350 ph
> 617-889-0929 ph
> 952-674-7253 fax <--- new
> Page: pageme@murrain.net
> AIM:pearlbear0 ICQ:129250575
> Skype: pearlbear
> Jabber: pearlbear@transactim.net
>
> "I see all the different religious traditions as paths for the
> development of inner peace, which is the true foundation of world
> peace. These ancient traditions come to us as a gift from our common
> past. Will we continue to cherish it as a gift and hand it over to the
> future generations as a legacy of our shared desire for peace?" - His
> Holiness the Dalai Lama
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: help on a query
@ 2004-10-08 04:44 sad <sad@bankir.ru>
parent: Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 0 replies; 8+ messages in thread
From: sad @ 2004-10-08 04:44 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; mmurrain@dbdes.com; +Cc: pgsql-sql
On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote:
> A query that should get the job done is:
>
> SELECT registration_id
> FROM registrations r
> WHERE NOT EXISTS (
> SELECT 1
> FROM receipts
> WHERE registration_id = r.registration_id
> );
Don't, PLEASE, don't !!!
drive this way :
SELECT r.registration_id
FROM registrations AS r
LEFT OUTER JOIN receipts AS rec
ON rec.registration_id = r.registration_id
WHERE rec.registration_id IS NULL;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: help on a query
@ 2004-10-08 13:02 CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 2 replies; 8+ messages in thread
From: CHRIS HOOVER @ 2004-10-08 13:02 UTC (permalink / raw)
To: sad@bankir.ru; +Cc: Thomas F O'Connell <tfo@sitening.com>; pgsql-sql; mmurrain@dbdes.com
Just curious, what is wrong with the first way of coding the solution?
------------------( Forwarded letter 1 follows )---------------------
Date: Fri, 8 Oct 2004 08:44:23 +0400
To: Thomas.F.O'Connell[tfo]@sitening.com.comp, mmurrain@dbdes.com.comp
Cc: pgsql-sql@postgresql.org.comp
From: sad@bankir.ru.comp
Sender: pgsql-sql-owner+m19150@postgresql.org.comp
Subject: Re: [SQL] help on a query
On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote:
> A query that should get the job done is:
>
> SELECT registration_id
> FROM registrations r
> WHERE NOT EXISTS (
> SELECT 1
> FROM receipts
> WHERE registration_id = r.registration_id
> );
Don't, PLEASE, don't !!!
drive this way :
SELECT r.registration_id
FROM registrations AS r
LEFT OUTER JOIN receipts AS rec
ON rec.registration_id = r.registration_id
WHERE rec.registration_id IS NULL;
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: help on a query
@ 2004-10-08 13:03 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
1 sibling, 0 replies; 8+ messages in thread
From: Achilleus Mantzios @ 2004-10-08 13:03 UTC (permalink / raw)
To: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>; +Cc: sad@bankir.ru, "Thomas F O'Connell" <tfo@sitening.com>; pgsql-sql; mmurrain@dbdes.com
O CHRIS HOOVER Ýãñáøå óôéò Oct 8, 2004 :
> Just curious, what is wrong with the first way of coding the solution?
> ------------------( Forwarded letter 1 follows )---------------------
> Date: Fri, 8 Oct 2004 08:44:23 +0400
> To: Thomas.F.O'Connell[tfo]@sitening.com.comp, mmurrain@dbdes.com.comp
> Cc: pgsql-sql@postgresql.org.comp
> From: sad@bankir.ru.comp
> Sender: pgsql-sql-owner+m19150@postgresql.org.comp
> Subject: Re: [SQL] help on a query
>
> On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote:
> > A query that should get the job done is:
> >
> > SELECT registration_id
> > FROM registrations r
> > WHERE NOT EXISTS (
> > SELECT 1
> > FROM receipts
> > WHERE registration_id = r.registration_id
> > );
>
> Don't, PLEASE, don't !!!
>
> drive this way :
>
> SELECT r.registration_id
> FROM registrations AS r
> LEFT OUTER JOIN receipts AS rec
> ON rec.registration_id = r.registration_id
> WHERE rec.registration_id IS NULL;
Some one could add the same dont please dont, and advise:
select registration_id FROM registrations
where registration_id not in (select registration_id from receipts);
but its actually version/data dependent i suppose.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
-Achilleus
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: help on a query
@ 2004-10-08 15:06 Thomas F.O'Connell <tfo@sitening.com>
parent: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
1 sibling, 1 reply; 8+ messages in thread
From: Thomas F.O'Connell @ 2004-10-08 15:06 UTC (permalink / raw)
To: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>; +Cc: sad@bankir.ru; pgsql-sql; mmurrain@dbdes.com
I think the OUTER JOIN version is probably more efficient, but EXPLAIN
would tell you.
-tfo
On Oct 8, 2004, at 8:02 AM, CHRIS HOOVER wrote:
> Just curious, what is wrong with the first way of coding the solution?
> ------------------( Forwarded letter 1 follows )---------------------
> Date: Fri, 8 Oct 2004 08:44:23 +0400
> To: Thomas.F.O'Connell[tfo]@sitening.com.comp, mmurrain@dbdes.com.comp
> Cc: pgsql-sql@postgresql.org.comp
> From: sad@bankir.ru.comp
> Sender: pgsql-sql-owner+m19150@postgresql.org.comp
> Subject: Re: [SQL] help on a query
>
> On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote:
>> A query that should get the job done is:
>>
>> SELECT registration_id
>> FROM registrations r
>> WHERE NOT EXISTS (
>> SELECT 1
>> FROM receipts
>> WHERE registration_id = r.registration_id
>> );
>
> Don't, PLEASE, don't !!!
>
> drive this way :
>
> SELECT r.registration_id
> FROM registrations AS r
> LEFT OUTER JOIN receipts AS rec
> ON rec.registration_id = r.registration_id
> WHERE rec.registration_id IS NULL;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: help on a query
@ 2004-10-08 15:19 Michelle Murrain <tech@murrain.net>
parent: Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 8+ messages in thread
From: Michelle Murrain @ 2004-10-08 15:19 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>; sad@bankir.ru; pgsql-sql
Thomas F.O'Connell wrote:
> I think the OUTER JOIN version is probably more efficient, but EXPLAIN
> would tell you.
Well, this all makes me feel better. For everyone's edification:
select registration_id FROM registrations
where registration_id not in (select registration_id from receipts);
Generates 0 rows
The OUTER JOIN version is quite a bit more efficient (by an order of
magnitude) than the option with WHERE NOT EXISTS subquery.
Thanks all for the help. Saved my butt again!!
--
.Michelle
--------------------------
Michelle Murrain
mmurrain at dbdes dot com
413-222-6350 ph
617-889-0929 ph
952-674-7253 fax <--- new
Page: pageme@murrain.net
AIM:pearlbear0 ICQ:129250575
Skype: pearlbear
Jabber: pearlbear@transactim.net
"I see all the different religious traditions as paths for the
development of inner peace, which is the true foundation of world peace.
These ancient traditions come to us as a gift from our common past. Will
we continue to cherish it as a gift and hand it over to the future
generations as a legacy of our shared desire for peace?" - His Holiness
the Dalai Lama
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: help on a query
@ 2004-10-09 03:17 Greg Stark <gsstark@mit.edu>
parent: Michelle Murrain <tech@murrain.net>
0 siblings, 0 replies; 8+ messages in thread
From: Greg Stark @ 2004-10-09 03:17 UTC (permalink / raw)
To: mmurrain@dbdes.com; +Cc: Thomas F.O'Connell <tfo@sitening.com>; CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>; sad@bankir.ru; pgsql-sql
Michelle Murrain <tech@murrain.net> writes:
> The OUTER JOIN version is quite a bit more efficient (by an order of magnitude)
> than the option with WHERE NOT EXISTS subquery.
This is going to be heavily dependent on the version of postgres. IN/NOT IN
execution has improved a lot in 7.4 and later. If you're still on 7.3 then the
outer join will be better. But in 7.4 I would actually expect the NOT IN or
the NOT EXISTS to be faster.
--
greg
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-10-09 03:17 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-08 03:03 help on a query Michelle Murrain <tech@murrain.net>
2004-10-08 03:10 ` Thomas F.O'Connell <tfo@sitening.com>
2004-10-08 04:44 ` sad <sad@bankir.ru>
2004-10-08 13:02 Re: help on a query CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
2004-10-08 13:03 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-10-08 15:06 ` Thomas F.O'Connell <tfo@sitening.com>
2004-10-08 15:19 ` Michelle Murrain <tech@murrain.net>
2004-10-09 03:17 ` Greg Stark <gsstark@mit.edu>
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