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