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: 331

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

Related posts about tsql

Related posts about sql-server