Subject: Re: How to find entries missing in 2nd table?
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: pgsql-sql@postgresql.org
In-Reply-To: <20060711141951.GA17936@s2.yuriev.com>
References: <20060711141951.GA17936@s2.yuriev.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1152633646.6540.68.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Tue, 11 Jul 2006 11:00:46 -0500

On Tue, 2006-07-11 at 09:19, alex-lists-pgsql@yuriev.com wrote:
> Hi,
> 	I realize I probably lost my marbles but I've been having a god
> awful time with a single query:
> 
> control:
> 	....
> 	....
> 	controller_id	pk;
> 
> 
> datapack:
> 
> 	controller_id	fk;
> 	....
> 	....
> 	....
> 
> I need to get all entries from the table control that are not listed in
> datapack.

OK, this is a pretty common problem.  Easy solution is left join / null.

select t1.id from table1 t1 left join table table2 t2 on (t1.id=t2.id)
where t2.id is null

since a left join gives us all rows from the left table, and nulls where
the right table doesn't match up, and we're asking for all the rows
where t2.id is null, we'll get all the rows in t1 that don't have a
match in t2.  cool, huh?