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.
To enable the foreign key, I had to add an INDEX on this.
The query works fine, but I can't get only one strategy with it. I'll go for the first solution, I hope it won't lag with 8-leg strategy on a wide table...
-- 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]
