From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Selecting records not present in related tables
Date: Thu, 06 Oct 2005 16:28:45 -0400
Message-ID: <5.2.1.1.0.20051006162629.03400300@pop6.sympatico.ca> (raw)
In-Reply-To: <25266c5c0510061243g13c3f49cudde160c67f961bca@mail.gmail.co m>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fbax@sympatico.ca
Subject: Re: Selecting records not present in related tables
In-Reply-To: <5.2.1.1.0.20051006162629.03400300@pop6.sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox