Re: finding records not listed in a column, Postgresql

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.org@paulm.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