From: | Paul Makepeace <postgresql(dot)org(at)paulm(dot)com> |
---|---|
To: | Aaron Payne <apayneinc(at)yahoo(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: finding records not listed in a column, Postgresql |
Date: | 2003-04-27 15:28:19 |
Message-ID: | 20030427152819.GY11454@mythix.realprogrammers.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2003-04-27 15:36:18 | Re: finding records not listed in a column, Postgresql |
Previous Message | Aaron Payne | 2003-04-27 15:02:16 | finding records not listed in a column, Postgresql |