March 7, 2006 at 7:43 am
Guys hi, one easy question, greatful if you could answer it...
I have a midlle table that connects users and addresses.
the table is of the form
user_fk, address_fk.
it is higly possible, some users have 2 addresses. That is their user_fk is contained twice (with diferent address_fk) in the table. My question is how to find those users that have 2 addresses (2 address_fk). I know they are 78 in number, but how can i trace the exact records? 🙂
easy one, please answer.. 🙂
March 7, 2006 at 8:07 am
select adress_fk, count(user_fk)
from middle_table
group by address_fk
having count(*) > 1
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 7, 2006 at 8:27 am
Steve hi, thank you for your help.
it seems i am doing something wrong.
after your suggestion i wrote the following
select
addr_fk
,count(user_fk)
from
ecdldb.dbo.UserAddr
group by addr_fk
having count(*)>1
but it returns null records! however i see "duplicate" records (same user id with different addresses ) like the one i show you here, but are not presented in my results... further help pls?
User_fk Addr_fk
780579 102261
780579 102263
March 7, 2006 at 8:56 am
Try this:
declare @UserAddr table (user_fk int, addr_fk int)
insert @UserAddr values(780579, 102261)
insert @UserAddr values(780579, 102263)
--return just the user_fk
select user_fk
from @UserAddr
group by user_fk
having count(*)>1
--return just the user_fk and addr_fk
select ua.*
from @UserAddr ua
inner join (select user_fk
from @UserAddr
group by user_fk
having count(*)>1) dups
on ua.user_fk = dups.user_fk
March 7, 2006 at 9:25 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply