Re: Filtering by tags

On Wed, Jun 30, 2010 at 05:54:51PM +0200, Anders Steinlein wrote:
> No one with any response on this?

Fun problem, how about:

SELECT x.email, x.segmentid
FROM (
SELECT c.email, t.segmentid, t.tagname, t.tagtype
FROM contacts c, segments_tags t) x
LEFT JOIN contacts_tags t USING (email,tagname)
GROUP BY x.email, x.segmentid
HAVING NOT bool_or((x.tagtype = 0) <> (t.tagname IS NULL));

The HAVING statement is a little obscure, but could also be written:

HAVING COUNT(CASE WHEN x.tagtype = 0 AND t.tagname IS NULL THEN 1 END) = 0
AND COUNT(CASE WHEN x.tagtype = 1 AND t.tagname IS NOT NULL THEN 1 END) = 0;

it works by keeping count of the number of "bad" tags; i.e. if the tag
type is zero then expect the tag entry not to be found, and the reverse
if the tag type is one.

Because of the cross join in the inner select this is going to be
*slow*, so you may want to limit things a bit by only working with one
contact or segment type at a time.

Hope that gives you a few ideas!

--
Sam http://samason.me.uk/