select only those rows where ssn matches previous ssn

[sue1127]

Hi,

I'm using Sybase 11.0.3. There are some people who have multiple account id's. I would like to select only those people, based on the fact that they have the same social security number. Since Previous is not a function, I can't say where Previous(ssn)= ssn. Does anyone have any suggestions on how I can do this?

Thanks,

Sue

 

[nicatt]

You could try something like a self join of the table. It should give you all the ssn's that are equal.

Select distinct s1.ssn
From ssntable s1, ssntable s2
Where s1.ssn = s2.ssn
and s1.othervalue != s2.othervalue

othervalue is some value in the records that would be different. Look for a primary key.

If no other values are different between the records you
could create a primary key using a numeric identity column and use it as the othervalue.

 

[sue1127]

nicatt,

Actually the account id's are not in the same table as the ssn,last name and first name. I tried self-joining each of the tables to itself, but I couldn't get it to work correctly.

I fooled around with count and came up with this:

SELECT
Person.last_name, Person.first_name,
count (distinct person.ssn)
FROM
Qdb.dbo.Person Person,
Qdb.dbo.ReceivableAccount ReceivableAccount,
Qdb.dbo.ReceivableAccountStudent ReceivableAccountStudent
WHERE
Person.Person_ID = ReceivableAccount.Bill_To_ID and
ReceivableAccount.Account_ID *= ReceivableAccountStudent.Account_ID


group by person.ssn
Having count(person.ssn) > 1

This sql sentence shows me the last and first name, but I can't get it to work correctly and show me the ssn or the account_id. I was thinking that I might try creating the sql as a stored procedure and feeding it into Crystal Reports, and maybe in that way I could display the additional fields.

Thanks,

Sue

 

[4121]

I think group by clause should have Person.last_name, Person.first_name and in select clause you should take out distinct i-e count(person.ssn).
SELECT
Person.last_name, Person.first_name,
count (person.ssn)
FROM
Qdb.dbo.Person Person,
Qdb.dbo.ReceivableAccount ReceivableAccount,
Qdb.dbo.ReceivableAccountStudent ReceivableAccountStudent
WHERE
Person.Person_ID = ReceivableAccount.Bill_To_ID and
ReceivableAccount.Account_ID = ReceivableAccountStudent.Account_ID
group by Person.last_name, Person.first_name
Having count(person.ssn) > 1

Try it! Good Luck.