Select with normalized table

[Philippe Poelvoorde]

Hi,
Here are the tables :
CREATE TABLE markets (
        Id integer NOT NULL auto_increment,
        ContractCode varchar(20),
        PRIMARY KEY (Id),
) TYPE=InnoDB;
CREATE TABLE strategies (
        Id integer NOT NULL auto_increment,
        StrategyCode char(15) NOT NULL DEFAULT '',
        PRIMARY KEY(Id)
) TYPE=InnoDB;
CREATE TABLE strategies_markets (
        StrategyId integer NOT NULL DEFAULT '0',
        MarketId integer NOT NULL DEFAULT '0',
        Ratio integer NOT NULL DEFAULT '1',
        PRIMARY KEY(StrategyId, MarketId),
        FOREIGN KEY (StrategyId) REFERENCES strategies(Id),
        FOREIGN KEY (MarketId) REFERENCES markets(Id)
) Type=InnoDB;
A particular stategy gather at least one market, and each market can be 
in several strategies (or none). So strategies_markets represent this 1 
to N relationship.
Here is my question:
I have an incoming array of markets and I need to find if there is a 
strategy that correspond to that particular set of markets. Is there any 
particular way of doing that ?
I would think of the following for a strategy with 3 legs (strategies 
can involve up to 8 legs in my case) :

SELECT
 sm1.StrategyId
FROM
 strategies_markets AS sm1,
 strategies_markets AS sm2,
 strategies_markets AS sm3
WHERE
 sm1.MarketId ='the incoming market Id1' AND
 sm2.MarketId ='the incoming market Id2' AND
 sm3.MarketId ='the incoming market Id3' AND
 sm1.StrategyId = sm2.StrategyId AND
 sm2.StrategyId = sm3.StrategyId
Can anyone think of a simpler solution involving maybe less join, a 
IN(), or anything else ?
Thanks.

--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]