X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id AA060DAE3A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 22 Nov 2005 04:31:23 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 32245-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 22 Nov 2005 08:31:24 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2])
	by svr1.postgresql.org (Postfix) with ESMTP id C25FEDA912
	for <pgsql-sql@postgresql.org>; Tue, 22 Nov 2005 04:31:20 -0400 (AST)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 01BE131059; Tue, 22 Nov 2005 09:31:22 +0100 (MET)
From: "Sim Zacks" <sim@compulab.co.il>
X-Newsgroups: pgsql.sql
Subject: join if all matches
Date: Tue, 22 Nov 2005 10:30:17 +0200
Organization: Hub.Org Networking Services
Lines: 53
Message-ID: <dlul0l$ivg$1@news.hub.org>
X-Complaints-To: usenet@news.hub.org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/256
X-Sequence-Number: 23507

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?