join if all matches

From: "Sim Zacks" <sim(at)compulab(dot)co(dot)il>
To: pgsql-sql(at)postgresql(dot)org
Subject: join if all matches
Date: 2005-11-22 08:30:17
Message-ID: dlul0l$ivg$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2005-11-22 09:14:04 Re: tid_le comparison for tuple id (ctid) values?
Previous Message Samer Abukhait 2005-11-22 05:55:13 Re: Please help to wite the constraint.