Message-Id: <5.2.1.1.0.20051006162629.03400300@pop6.sympatico.ca>
Date: Thu, 06 Oct 2005 16:28:45 -0400
To: pgsql-sql@postgresql.org
From: Frank Bax <fbax@sympatico.ca>
Subject: Re: Selecting records not present in related tables
In-Reply-To: <25266c5c0510061243g13c3f49cudde160c67f961bca@mail.gmail.co
 m>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

At 03:43 PM 10/6/05, Hector Rosas wrote:

>Hello, I'm trying to select records in a table not present in a related 
>table, in example, I've a table with message information (subject, 
>message, date, etc) and another (usermessages) with where user(s) has that 
>message, its state, etc. Records in this table will be deleted in a 
>certain time (just some extra info).
>I want to select messages records that aren't present in the other table 
>(usermessages), I got the next two queries, maybe someone can suggest a 
>better one.
>
>SELECT <http://m.id>m.id FROM messages AS m
>WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=m.id )=0;
>
>SELECT <http://m.id>m.id FROM messages AS m where id NOT IN (select 
>um.idmessage FROM usermessages um);


select m.id from messages as m left join usermessages as um on 
m.id=um.idmessage where um.idmessage is null;