Re: Selecting records not present in related tables

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;