Re: join if all matches

On Tue, Nov 22, 2005 at 10:30:17 +0200,
Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> I am trying to figure out an sql statement and I was hoping someone could
> help. I'm having brainfreeze right now.
>
> Table Rules
> RuleID
> RuleName
>
> Table RuleAgents
> RuleAgentID
> RuleID
> Agent
>
> Table RuleActions
> RuleActionID
> RuleID
> Action
>
> I am passing in an array of agents into a function and I would like to see
> all the actions for which all of the agents of a rule have been found.
>
> For example:
>
> Rules
> RuleID RuleName
> 1 Rule1
> 2 Rule2
>
> RuleAgents
> RuleAgentID RuleID Agent
> 1 1 15
> 2 1 17
> 3 2 91
>
> RuleActions
> RuleActionID RuleID Action
> 1 1 1000
> 2 1 1005
> 3 1 1010
> 4 1 1099
> 5 2 1500
> 6 2 9807
> 7 2 1409
>
> If I pass into my function 15 then I don't want it to return anything,
> because rule1 requires both 15 and 17.
> If I pass in 19 then I want it to return a resultset including the actions
> with Rule2 (1500,9807,1409)
> If I pass in both 15 and 17 then I want it to return all the actions with
> Rule1
>
> Any thoughts on the join?

One approach is to first eliminate rule agents that have agents in your
list. Then any remaining rules in ruleagents are rules that should be
removed from the rule list. Then you canoutput ruleactions that have
rules in this modified list.
You should be able to build a query doing this using a couple of levels
of IN / NOT IN.