Re: How to find entries missing in 2nd table?

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?