March 16, 2011 at 3:10 pm
DB: SQL Server 2008
I want to select id, title and userid from a table if that userid occurs more than once across 5 tables (and perhaps more in the future).
For example with 3 tables:
So if a user has 2 records in [locations] table, and 1 in [artists] table, and 1 in [users] table i want to return the id, title and userid of all 4 records.
This is what I have now, but it returns 0 records.
When I leave out the "having count(userid)>1" part, I get ALL 400 records in all tables.
select userid,id,title from (
select id,title,userid from locations l
union
select id,title,userid from artists a
union
select id,title,userid from users u
) as info
group by userid,id,title
having count(userid)>1
March 16, 2011 at 3:22 pm
Use UNION ALL, not Union. Union forces a distinct out of the final result.
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
March 16, 2011 at 3:59 pm
Ok, I now have:
select userid,id,title from (
select id,title,userid from locations l
union all
select id,title,userid from artists a
union all
select id,title,userid from users u
) as info
group by userid,id,title
--having count(userid)>1
but as soon as I uncomment the having count part, I still get 0 results
March 16, 2011 at 4:53 pm
The code works when I run it. Are the title's identical? The code snippet is written such that the grouping includes the title.
HTH
Elliott
March 16, 2011 at 5:09 pm
No, titles and id's are different in this case. But thats a good point because I DO want ALL records to be returned, even when the title's OR id's are the same.
But perhaps I've failed to describe my requirement:
I want to return ALL records from ALL these tables if a specific userid has more than 1 record in ANY of these tables.
the data im working with. So in the case below I would have 5 records returned with id's: 5,7,9,10 and 12
[locations]
id title userid
5 test 32DD30EB-1691-457B-9FF5-FC41D687E579
7 test2 32DD30EB-1691-457B-9FF5-FC41D687E579
12 test 32DD30EB-1691-457B-9FF5-FC41D687E579
91 other FA189A61-7BAB-492E-BCF7-9E2818362B06
[artists]
id title userid
9 hello 32DD30EB-1691-457B-9FF5-FC41D687E579
[users]
id title userid
10 john 32DD30EB-1691-457B-9FF5-FC41D687E579
18 bla D646DA93-B511-4CB3-8F00-7468A9AA1F0D
March 16, 2011 at 8:01 pm
Then group on the unionall of userid alone, then use that as a subquery back to the different tables to return the records.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply