July 11, 2011 at 10:54 am
Using Paul's temp table, here is an alternate qry using divide & conquer (90's oldies).
SELECT t.*
FROM #mycodes t
INNER JOIN (
SELECT groupid
FROM
(SELECT groupid, mytype FROM #mycodes GROUP BY groupid, mytype HAVING count(*)>1) x -- filter mytype
GROUP BY groupid
HAVING count(*)>1 -- filter group
) y on t.groupid=y.groupid -- list qualified groups
Not sure about scalability but exec-plan looks OK.
July 11, 2011 at 11:00 am
Flexdog (7/11/2011)
Using Paul's temp table, here is an alternate qry using divide & conquer (90's oldies).
Looks fine to me also. Very similar to the EXISTS version posted at http://www.sqlservercentral.com/Forums/FindPost1139404.aspx
July 11, 2011 at 11:12 am
Yes, similar to EXISTS however the original post omitted the groupid predicate and pain-avoidance from the past I don't like EXISTS in the WHERE clause - perhaps sql4.3 days spooked me ever since :hehe:
July 11, 2011 at 11:46 am
Flexdog (7/11/2011)
Yes, similar to EXISTS however the original post omitted the groupid predicate...
I don't see a missing predicate. Do you mean a column name?
...and pain-avoidance from the past I don't like EXISTS in the WHERE clause - perhaps sql4.3 days spooked me ever since :hehe:
Perhaps. There is nothing wrong with using EXISTS in the WHERE clause now (or for as long as I can remember), in fact in many cases (though not here) a semi-join is optimal since it can stop looking as soon as a match is found - a JOIN must process the whole set.
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply