From: | Frank Bax <fbax(at)sympatico(dot)ca> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Selecting records not present in related tables |
Date: | 2005-10-06 20:28:45 |
Message-ID: | 5.2.1.1.0.20051006162629.03400300@pop6.sympatico.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2005-10-06 20:39:11 | Re: MOVE in SQL vs PLPGSQL |
Previous Message | Scott Marlowe | 2005-10-06 20:13:41 | Re: Selecting records not present in related tables |