Re: Select with normalized table

Johan Pettersson Tue, 21 Sep 2004 04:05:05 -0700

Hi Philippe,

how about this:

SELECT  strategyid, COUNT(marketid) cnt
FROM    strategies_markets
WHERE   marketid in(<selected markets here>)
GROUP BY strategyid
ORDER BY cnt DESC;

The resultset should contain the best matches, in descending order. To find strategies that contains all markets (but that may contain others) you could use:

SELECT  strategyid, COUNT(marketid) cnt
FROM    strategies_markets
WHERE   marketid in(<selected markets here>)
GROUP BY strategyid
HAVING cnt = <number of selected markets>

If you reverse the key-order in the primary key of strategies_markets, this baby should really fly.

Regards,
Johan

2004-09-21 kl. 11.51 skrev 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]

----

Johan Pettersson
OnGame e-solutions AB
Centralplan 19
SE-111 20 Stockholm
Sweden

Mobile: +46 (0)704 335 345
Email:   [EMAIL PROTECTED]


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

Re: Select with normalized table

Reply via email to