Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 tsql help

Author  Topic 

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-09-14 : 23:14:36
this query:
select strssn,zadid,strfirstname,strlastname,vartransactionid from vwcomplete order by strssn

gives me results like this:
111111111 9999 Jaunice Green 3013321
111111111 10000 Jaunice Green 3066871
222222222 10000 Julie Goodknecht 86632
333333333 9999 Merlin Harris 3043126
444444444 10000 Katherine Corriveau 429289
444444444 10000 Katherine Corriveau 429373
444444444 10000 Katherine Corriveau 471121

what i need is to get only records that have 2 records with the same ssn and different zadid's. for example, in the results above, i would only want the first 2 records. 2 records where ssn is 111111111 and the zadid is different. how do i accomplish this?

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-14 : 23:26:48
hi mike -

I think this is what you want -
select distinct a.strssn, a.zadid, a.strfirstname, a.strlastname, a.vartransactionid 
from vwcomplete a, vwcomplete b
where a.strssn = b.strssn and a.zadid <> b.zadid
order by a.strssn


let me know if that's not it...(not sure what you wanted to do if there were more than 2 records etc)

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-15 : 00:18:49
is this what you're looking for?


select * from vwcomplete where
strsssn in (select strsssn
from vwcomplete
group by strsssn
having count(strsssn)=2)
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-15 : 00:20:51
jen, you're anwer doesn't take into account where the sssn is the same, but the zadid is not...

reading between the lines, I suspect the requirement is to list all records with the same ssn but not the same zadid - not just two - but who knows?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-15 : 01:41:44
when i tested it using his data, it came out as expected, the group by groups the data into similar strssn and then counts the number of strssn in the group if equal to two then similar strssn by two.

his example's expected result is two records...

thanks for the feedback though...

quote:
Originally posted by rrb

jen, you're anwer doesn't take into account where the sssn is the same, but the zadid is not...

reading between the lines, I suspect the requirement is to list all records with the same ssn but not the same zadid - not just two - but who knows?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-15 : 01:43:52
Sorry -didn't mean it in a nasty way...

I'll be keen to hear if he's going to clarify
quote:
what i need is to get only records that have 2 records with the same ssn and different zadid's




--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-15 : 01:48:59
don't worry, it's taken as feedback...
Go to Top of Page
   

- Advertisement -