Skip site navigation (1) Skip section navigation (2)

Re: help on a query

From: Thomas F(dot)O'Connell <tfo(at)sitening(dot)com>
To: mmurrain(at)dbdes(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: help on a query
Date: 2004-10-08 03:10:33
Message-ID: 9E089E3A-18D7-11D9-936A-000D93AE0944@sitening.com (view raw or flat)
Thread:
Lists: 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(at)murrain(dot)net
> AIM:pearlbear0 ICQ:129250575
> Skype: pearlbear
> Jabber: pearlbear(at)transactim(dot)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


In response to

Responses

pgsql-sql by date

Next:From: sadDate: 2004-10-08 04:44:23
Subject: Re: help on a query
Previous:From: Michelle MurrainDate: 2004-10-08 03:03:07
Subject: help on a query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group