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

Re: finding records not listed in a column, Postgresql

From: Aaron Payne <apayneinc(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: Paul Makepeace <postgresql(dot)org(at)paulm(dot)com>
Subject: Re: finding records not listed in a column, Postgresql
Date: 2003-04-28 11:52:08
Message-ID: 20030428115208.21149.qmail@web40613.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Guys,

Thanks, that's exactly what I was looking to find!

Here's the final version:
select People.person_id, ci.collectionitem_id as c_id,
ci.objectType as c_oT

from People left join CollectionItems as ci on

People.objectID=ci.objectID where ci.objectID is NULL

Aaron


--- Paul Makepeace <postgresql(dot)org(at)paulm(dot)com> wrote:
> On Sun, Apr 27, 2003 at 08:02:16AM -0700, Aaron
> Payne wrote:
> > Hi,
> > 
> > I need the records in table A in which the values
> in
> > A.objectID are not listed in B.objectID.  I'm such
> a
> > noob that I'm not sure of the terms I need to use
> for
> > this statement.
> > 
> > table A
> > rows: person_id, objectID
> > 
> > table B
> > rows: id, objectID
> 
> Hi Aaron,
> 
> You could do it with a subselect or a left join.
> 
> create table A (person_id int, objectID int);
> create table B (id int, objectID int);
> insert into A (person_id, objectID) values (1, 2);
> insert into A (person_id, objectID) values (2, 3);
> insert into A (person_id, objectID) values (3, 4);
> insert into B (id, objectID) values (10, 4);
> insert into B (id, objectID) values (11, 3);
> 
> test=> select * from A where objectID not in (select
> objectID from B);
>  person_id | objectid 
> -----------+----------
>          1 |        2
> (1 row)
> 
> test=> select * from A left join B on
> A.objectID=B.objectID where B.objectID is NULL;
>  person_id | objectid | id | objectid 
> -----------+----------+----+----------
>          1 |        2 |    |         
> (1 row)
> 
> test=>
> 
> Left join in essence (as I understand it!) returns
> all rows from A, and
> those of B that match the ON condition. Those in B
> that don't match
> have a NULLi result.
> 
> Note that the * in the left join example returns
> columns from both
> tables so you may want to use the table.column
> format to get the columns
> you actually need. (Use of * is frowned on in "real"
> code.)
> 
> Cheers, Paul
> 
> -- 
> Paul Makepeace
> .......................................
> http://paulm.com/
> 
> "If you knew what I know, then the tea-leaves will
> reveal all, in good
>  time."
>    -- http://paulm.com/toys/surrealism/

=====
thanks,
Aaron
www.aaronpayne.com

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com


In response to

pgsql-novice by date

Next:From: Charles ChristiansenDate: 2003-04-28 12:34:55
Subject: Returning multiple cursors/resultsets from PostgreSQL procedure
Previous:From: Sepp RudelDate: 2003-04-28 08:57:40
Subject: Q: timestamp - timestamp

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