X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 52ABC9DCA9F for ; Tue, 29 Nov 2005 01:01:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08819-04 for ; Tue, 29 Nov 2005 01:01:26 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 23EF79DCAA9 for ; Tue, 29 Nov 2005 01:01:24 -0400 (AST) Received: (qmail 17186 invoked by uid 500); 29 Nov 2005 05:05:16 -0000 Date: Mon, 28 Nov 2005 23:05:16 -0600 From: Bruno Wolff III To: Sim Zacks Cc: pgsql-sql@postgresql.org Subject: Re: join if all matches Message-ID: <20051129050516.GA15942@wolff.to> Mail-Followup-To: Bruno Wolff III , Sim Zacks , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/265 X-Sequence-Number: 23516 On Tue, Nov 22, 2005 at 10:30:17 +0200, Sim Zacks 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.