Thread: finding records not listed in a column, Postgresql
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 thanks, Aaron ===== thanks, Aaron www.aaronpayne.com __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com
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/
On Sun, Apr 27, 2003 at 08:02:16 -0700, Aaron Payne <apayneinc@yahoo.com> 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 Below are two ways of doing this. If A.objectID and B.objectID can both be NULL, the records in A with NULL values won't be excluded. select * from A where not exists (select 1 from B where A.objectID = B.objectID); select A.person_id, A.objectID from A right join B using (objectID) where B.objectID is null;
Paul Makepeace <postgresql.org@paulm.com> writes:
> On Sun, Apr 27, 2003 at 08:02:16AM -0700, Aaron Payne wrote:
>> I need the records in table A in which the values in
>> A.objectID are not listed in B.objectID.
> select * from A where objectID not in (select objectID from B);
This is the bog-standard way of doing it, but performance sucks in
current Postgres releases (although 7.4 will change that). So people
tend immediately to look for workarounds. The "EXISTS" hack illustrated
in the PG FAQ (item 4.22) is one pretty good way.
> select A.* from A left join B on A.objectID=B.objectID where B.objectID is NULL;
This is a good way only if B.objectID is a unique column --- otherwise
you will get multiple copies of any A row that has multiple matches in
B. (You could possibly fix that by adding DISTINCT, but at the risk of
losing the performance gain you're after.)
regards, tom lane
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