pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin if all matches
2+ messages / 2 participants
[nested] [flat]
* join if all matches
@ 2005-11-22 08:30 Sim Zacks <sim@compulab.co.il>
0 siblings, 1 reply; 2+ messages in thread
From: Sim Zacks @ 2005-11-22 08:30 UTC (permalink / raw)
To: 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
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: join if all matches
@ 2005-11-29 05:05 Bruno Wolff III <bruno@wolff.to>
parent: Sim Zacks <sim@compulab.co.il>
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2005-11-29 05:05 UTC (permalink / raw)
To: Sim Zacks <sim@compulab.co.il>; +Cc: pgsql-sql
On Tue, Nov 22, 2005 at 10:30:17 +0200,
Sim Zacks <sim@compulab.co.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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-11-29 05:05 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-22 08:30 join if all matches Sim Zacks <sim@compulab.co.il>
2005-11-29 05:05 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox