Thread: Removing simliar elements from a set

Hi folks,

I'm trying to remove items from a set which are similar to items in
another set.

This is the set (MASTER) from which I wish to remove items:

/ports/Mk/bsd.python.mk/ports/lang/python-doc-html/distinfo/ports/lang/python/Makefile/ports/lang/python/distinfo/ports/lang/python/files/patch-Modules-Setup.dist

These are the items (MATCHES) which are the prefixes which must be
removed:
/ports/lang/python-doc-html/ports/lang/python

In this case, the answer would be:
/ports/Mk/bsd.python.mk

In short, we remove all items from MASTER which are under the directories
specified in MATCHES.

My first attempt, which works only if MATCHES contains one item:

SELECT * FROM MASTER JOIN MATCHES   ON NOT (MASTER.pathname ~ ('^' || MATCHES.pathname || '/.+'));

However, if there is more than one row in MATCHES, this will not work.

Clues please?
-- 
Dan Langille - http://www.langille.org/

Dan,

> I'm trying to remove items from a set which are similar to items in
> another set.
<snip>

> In short, we remove all items from MASTER which are under the directories
> specified in MATCHES.

from your example, you are trying to remove all directories which do *not*
match.   What do you want, exactly?

--
-Josh BerkusAglio Database SolutionsSan Francisco

On 26 Sep 2003 at 16:55, Josh Berkus wrote:

> Dan, 
> 
> > I'm trying to remove items from a set which are similar to items in
> > another set.
> <snip>
> 
> > In short, we remove all items from MASTER which are under the directories
> > specified in MATCHES.
> 
> from your example, you are trying to remove all directories which do *not* 
> match.   What do you want, exactly?

Josh and I talked on IRC about this.

This is the result set I want:
/ports/Mk/bsd.python.mk

I want things from MASTER which do not match things in MATCHES.

Josh suggested this:

SELECT *  FROM master WHERE NOT EXISTS (  SELECT *    FROM matches     WHERE master.pathname LIKE (matches.pathname ||
'/%'));

Cheers.
-- 
Dan Langille : http://www.langille.org/