join if all matches - Mailing list pgsql-sql

From Sim Zacks
Subject join if all matches
Date
Msg-id dlul0l$ivg$1@news.hub.org
Whole thread Raw
Responses Re: join if all matches
List pgsql-sql
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

RuleAgentsRuleAgentID    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?




pgsql-sql by date:

Previous
From: "Wim Audenaert"
Date:
Subject: Drop functions
Next
From: Colton A Smith
Date:
Subject: MAX, MIN and arrays