Thread: help on a query
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
A query that should get the job done is: SELECT registration_id FROM registrations r WHERE NOT EXISTS (SELECT 1FROM receiptsWHERE 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
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_idFROM registrations AS r LEFT OUTER JOIN receipts AS recON rec.registration_id = r.registration_id WHERE rec.registration_id IS NULL;
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_idFROM registrations AS r
LEFT OUTER JOIN receipts AS recON 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
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
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;
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
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