February 15, 2011 at 2:31 pm
I want to select all rows where userid occurs more than once in table [user_artist_owned]
I have this SQL statement:
SELECT a.id,a.title,uao.userid
FROM user_artist_owned uao
INNER JOIN artists a on a.id=uao.objectid
where userid='32DD30EB-1691-457B-9FF5-FC41D687E579'
GROUP BY a.id,a.title,userid
--HAVING COUNT(userid)>1
resulting in this:
id title userid
1654 Test 32DD30EB-1691-457B-9FF5-FC41D687E579
1655 Test 2 32DD30EB-1691-457B-9FF5-FC41D687E579
When I uncomment this line:
--HAVING COUNT(userid)>1
the result is empty.
What I really want is that it would still return the same result.
By uncommenting the line I'd expect that I'd see all records where the SAME userid occurs more than once...but apparently this doesnt work...
February 15, 2011 at 2:59 pm
Right idea, but you need to look at the process a little differently.
First, you need to determine all the multiple userid's in user_artist_owned, so we start with a query like so:
SELECT
userid
FROM
user_artist_owned as uao
GROUP BY
userid
HAVING
count(*) > 1
Now that we have that list, we can restrict the outer query using a subquery:
SELECT
a.id,
a.title,
drv.userid
FROM
(SELECT
userid
FROM
user_artist_owned as uao
GROUP BY
userid
HAVING
count(*) > 1
) AS drv
INNER JOIN
artists a
on a.id = drv.objectid
where
drv.userid = '32DD30EB-1691-457B-9FF5-FC41D687E579'
Let me know if that doesn't make sense.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 15, 2011 at 3:01 pm
First, you have to identify the set of userIDs which exist more than once.
Try this:
SELECT a.id,a.title,uao.userid
FROM user_artist_owned uao
INNER JOIN artists a on a.id=uao.objectid
where userid in (
SELECT userid
FROM user_artist_owned uao
group by userid
having count(*) > 1
) dt
Edited to add: Dangit, Craig, you beat me to it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 15, 2011 at 3:07 pm
Thanks guys! Got it working! 🙂
February 15, 2011 at 6:41 pm
Or this?
; WITH CTE AS
(
SELECT a.id,a.title,uao.userid ,
CountOfRows = COUNT(*) OVER(PARTITION BY uao.userid)
FROM user_artist_owned uao
INNER JOIN artists a
on a.id=uao.objectid
where userid='32DD30EB-1691-457B-9FF5-FC41D687E579'
)
SELECT * FROM CTE
WHERE CountOfRows > 1
I sense, the other solutions will perform better on large datasets.
February 16, 2011 at 3:57 pm
My first thought was I would like to see the difference in the execution plans. If I get time to do some tests I'll post results back here.
By the way, Peter. If you are going to have a really large number of "duplicate" userIDs in production, you might be better off to store them into a #temp table, index it, and then join to it with a second query. An "IN" clause is not a happy thing if there are hundreds or thousands of values to test against.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply