Re: finding records not listed in a column, Postgresql

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/