Hello you all, I could use some help. I have a small 'address'-book application where I offer the feature of assigning tags to contacts. The flexibility this tags give to the user is absolutely wonderfull..

The tags are being used for a search-command that includes contacts based on some basic search criteria and that have one or more tags assigned. Example: Give me - all contacts that have a name="bla", - AND have assigned the following tags: "press" and "online".

I've got this working fine...

However, we now need to enhance the search-feature, where we can also exclude contacts that are assigned one or more tags. Example: Give me - all contacts that have a name="bla", - AND have assigned the following tags: "press" and "online", - AND do not have assigned the tags "to_be_deleted" and "no_more_mail"

The problem is that I don't see how to solve this new construct. (A simplified version of) the solution I have sofar contains the following tables:

Table: contact Fields: contactid (PK), firstname, lastname

Table: tag Fields: tagid (PK), name

Table: contact_tag Fields: contactid (PK), tagid (PK)

Anyone have any ideas on how to do this? Any help much appreciated!

Paul

God, I love these tricky questions.

Give me - all contacts that have a name="bla", - AND have assigned the following tags: "press" and "online", - AND do not have assigned the tags "to_be_deleted" and "no_more_mail"

Table: contact Fields: contactid (PK), firstname, lastname

Table: tag Fields: tagid (PK), name

Table: contact_tag Fields: contactid (PK), tagid (PK)

Check the following query:

SELECT * 
FROM contact
WHERE firstname = "bla"
AND contactid  IN 
(SELECT contactid FROM contact_tag INNER JOIN tag ON ... WHERE name = "press" )
AND contactid  IN 
(SELECT contactid FROM contact_tag INNER JOIN tag ON ... WHERE name = "online" )
AND contactid  NOT IN 
(SELECT contactid FROM contact_tag INNER JOIN tag ON ... WHERE name = "to_be_deleted" )
AND contactid  NOT IN 
(SELECT contactid FROM contact_tag INNER JOIN tag ON ... WHERE name = "no_more_mail" )

If the above query is the correct one, then you can use FieldCompareSetPredicate to implement the IN & NOT IN predicates.

Walaa, I'm glad you like those tricky questions AND come up with a perfect solution ... subqueries!!

I did not come further then just liking them

I tested it in SQL ... works super and it easy to understand as well. I'll go on and get it done in LLBLGEN code.

Thanx!!!

Paul

I'm glad you like those tricky questions AND come up with a perfect solution ... subqueries!!

I like puzzles, and the thing about SQL questions, is that they force me to use my brain . Sometimes I succeed and sometimes I fail, so I seek help.

Walaa, I've got it done in LLBLGEN code as well. It's working perfectly!!! Thanx again for the help!! Paul