Re: Searching for results with an unknown amount of data - Mailing list pgsql-sql

On Fri, Sep 02, 2005 at 20:40:24 +0100, DownLoad X <x_download@hotmail.com> wrote:
> Now, I want to find all objects that have at most properties 1,2,3, say (so 
> something with (1,2) is okay, as is (1,2,3)). I can't see a way to do this 
> -- can anyone help?

It sounds like you are trying to find all objects that do not have any
properties outside of a specific list. One way to get that list is:

SELECT a_id FROM a   WHERE     NOT EXISTS       (SELECT 1         FROM b         WHERE           b.a_id = a.a_id
  AND           b.property NOT IN (1, 2, 3)       )
 
;

This doesn't take into account the semantics of nulls. If your data can have
nulls in it, then you need to decide precisely what you want and adjust
the query appropiately.