set difference in SQL query
        Posted  
        
            by TheObserver
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by TheObserver
        
        
        
        Published on 2010-04-01T05:26:34Z
        Indexed on 
            2010/04/01
            5:33 UTC
        
        
        Read the original article
        Hit count: 384
        
I'm trying to select records with a statement
SELECT * 
FROM A 
WHERE 
  LEFT(B, 5) IN 
    (SELECT * FROM 
       (SELECT LEFT(A.B,5), COUNT(DISTINCT A.C) c_count 
        FROM A 
        GROUP BY LEFT(B,5)
       ) p1 
       WHERE p1.c_count = 1
     ) 
     AND C IN 
        (SELECT * FROM 
            (SELECT A.C , COUNT(DISTINCT LEFT(A.B,5)) b_count 
             FROM A 
             GROUP BY C
            ) p2 
          WHERE p2.b_count = 1)
which takes a long time to run ~15 sec.
Is there a better way of writing this SQL?
© Stack Overflow or respective owner