Hi, I have a query which looks like this... SELECT BLEAH, COUNT(DISTINCT R1,R2) FROM T1 WHERE FK = 1 GROUP BY BLEAH ;
Lets say that over 10 rows where FK = 1 it counts 5 distinct R1-R2 pairs in a single 'BLEAH' group BLEAH = 'Y'. Now I want to search the table for all FK's with the same number of R1-R2 pairs in the BLEAH group, and I do it like this... SELECT FK, COUNT(DISTINCT IF(BLEAH='Y',CONCAT(R1,"-",R2),NULL)) AS BLING GROUP BY FK HAVING BLING = 5; The problem is that CONCAT. I don't like the look of it. It makes me think that the optimizer dosn't stand a chance, which I am not sure if it does anyway (even though I have a "index x (R1,R2)". I would like to be able to say something like... ... IF(BLEAH='Y',R1,R2,NULL) ... But of course that messes up the IF syntax. Or I would like to say... ... IF(BLEAH='Y',ROW(R1,R2),NULL) ... But DISTINCT balls "ERROR 1241 (21000): Operand should contain 1 column(s)", which seems a bit strange, as in the first query we are passing it two columns. Same error occurs with this syntax... ... IF(BLEAH='Y',(R1,R2),NULL) ... Am I stuck doing my CONCAT? I like to keep the syntax general, as for a particular PK, BLEAH could have several values, in which case I stack up my thingies... SELECT FK, COUNT(DISTINCT IF(BLEAH='Y',CONCAT(R1,"-",R2),NULL)) AS BLING, COUNT(DISTINCT IF(BLEAH='N',CONCAT(R1,"-",R2),NULL)) AS BLANG, GROUP BY FK HAVING BLING = 5 AND BLANG = 5; For example. Would the "COUNT(DISTINCT" above get optimized if I was only looking at the values in one (indexed) column (R1 for example)? Cheers, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
