StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I find all the records that has the same last four digits of social security number?
text
Body
copied!<p>I work for a university and I am given an assignment to do some testing on the logic that a developer has made changes to. </p> <p>What I need: I need a SQL Server query that looks into a Persons table and finds records that has the same last four digits of Social Security numbers. In addition to that, I also want to see if these records (whose last four digits of SSN is the same) also has the same last name. </p> <p>Here's what I have so far:<br> --I am pretty new to SQL so I am stuck to figure out how I can only compare last four digits of SSN and also find students who has the same last four digits of SSN and same last name. </p> <pre><code>SELECT t1.PersonsID, t1.LastName, t1.GovernmentID, t2.PersonsID, t2.LastName, t2.GovernmentID FROM Persons t1, Persons t2 WHERE t1.GovernmentID = t2.GovernmentID AND t1.PersonsID <> t2.PersonsID </code></pre> <p>Any help would be appreciated.</p> <p>Thanks!</p> <p>After looking at what Joe said I made another attempt:</p> <pre><code> SELECT t1.cmn_personsID, t1.governmentID, t1.lastname, t2.cmn_personsID, t2.governmentID, t2.lastname, case when t1.LastName = t2.lastName then 1 else 0 end as SameLastName from CMN_Persons t1, cmn_persons t2 WHERE right(t1.GovernmentID, 4) = right(t2.GovernmentID, 4) AND t1.cmn_PersonsID <> t2.cmn_PersonsID </code></pre> <p>This thing has been running for five minutes now. when I tried using top 10, it didn't seem give me users with same last four digits of governmentID and who would have same last name.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload