postgres return values in one table but not in another

[Andrei]

Hello,
  
    You may want to take a look at foreign key constraints in the SQL 
specification. To be exact, having something like "CREATE TABLE a (UUID 
int PRIMARY KEY)" and "CREATE TABLE b(data varchar(256), UUID REFERENCES 
a ON DELETE CASCADE)" allows data from b to be automatically deleted 
when a row in a is deleted (see "ON DELETE CASCADE" and similar foreign 
key options).

    If you want to select items that do not exist (using the same above 
definition of table a and b), you may use a left join and rely on the 
fact that if the right row does nto exist the value will be null: 
"SELECT t_b.UUID from b t_b LEFT JOIN a t_a ON t_b.UUID=t_a.UUID WHERE 
t_a.UUID is NULL". Other options would be using "NOT EXISTS": "SELECT 
t_b.UUID FROM b t_b WHERE NOT EXISTS (SELECT * FROM a t_a WHERE 
t_a.UUID=t_b.UUID" or you could use "NOT IN":  "SELECT t_b.UUID FROM b 
t_b where t_b.UUID NOT IN (SELECT t_a.UUID FROM a t_a)".

I hope this helps. If it does not, please let me know and I'll try to 
come up with different ideas,
Regards,
Andrei Litvin

Madison Kelly wrote:

> Hi all,
>
>   I need to do something that I am sure is possible but I can't think 
> of the words to even properly search Google for an answer on...
>
>   I need to keep to tables in synch. There are three fields (UUID [a 
> serial number for a partition], the parent directory and the actual 
> file name) that are common between the two tables.
>
>   The first table (say table A) geta updated based on external 
> information. Once it is updated, I need to remove entries in table B 
> that no longer have a matching entry in table A and then look for 
> entries in table A that do not have a matching entry yet in table B 
> and add them.
>
>   Lance and I talked about this on the way into work and here is what 
> we hashed out (though the syntax is the question):
>
> - First I want to do a DELETE where ever an entry in table B has a 
> given UUID (the partition serial number) without a matching parent 
> directory and file name in table A. This purges stale info.
>
> - Next I want to say SELECT the parent directory and file name from 
> table A where a matching entry does not exist in table B. Then I will 
> do a 'while' loop to do the processing I need and write out the 
> missing data.
>
>   So I guess to put it simply, how can I do a join that returns the 
> value from one table when a match doesn't exit?
>
> Thanks all! I hope everyone is having a relaxing holiday!
>
> Madison
> -- 
> The Toronto Linux Users Group.      Meetings: http://tlug.ss.org
> TLUG requests: Linux topics, No HTML, wrap text below 80 columns
> How to UNSUBSCRIBE: http://tlug.ss.org/subscribe.shtml
>

--
The Toronto Linux Users Group.      Meetings: http://tlug.ss.org
TLUG requests: Linux topics, No HTML, wrap text below 80 columns
How to UNSUBSCRIBE: http://tlug.ss.org/subscribe.shtml