Re: Proper Join and check

Patrick Nelson wrote:
> If I have a one to many relation set on sym in each table is like... say
> this:
>
> CREATE TABLE "tableone" (
> "sym" varchar(5) NOT NULL,
> PRIMARY KEY ("sym") );
>
> and
>
> CREATE TABLE "tablemany" (
> "id" int4 NOT NULL DEFAULT nextval('res_id_seq'::text),
> "sym" varchar(5) REFERENCES tableone ON UPDATE CASCADE,
> PRIMARY KEY ("id") );
>
> Now these tables hold a lot of data so I want to do this correctly. How do
> I find the records in tableone that don't have any corresponding records in
> tablemany? In my application this shouldn't happen accept during the
> initial inserting in tableone which briefly is followed by inserting in
> tablemany.

The query you are looking for is:

select a.sym from tableone a
where a.sym not in (select b.sym from tablemany b
where b.sym = a.sym);

This query will run MUCH faster if you create a secondary index for tablemany (in fact, don't even try it without the index for any significant number or rows):

create index tablemany_sym on tablemany(sym);

A better solution though, would be to use triggers to update tablemany when tableone is updated, or to wrap both updates in a single transaction so that the tableone transaction can be rolled back if tablemany is not updated.

Not knowing what you are attempting to do and how the updates occur prevents me from going into any more detail than this.

I hope this helps.
--
____ | Billy G. Allie | Domain....: Bill(dot)Allie(at)mug(dot)org
| /| | 7436 Hartwell | MSN.......: B_G_Allie(at)email(dot)msn(dot)com
|-/-|----- | Dearborn, MI 48126|
|/ |LLIE | (313) 582-1540 |