Hi guys I need some help on this one I'm not sure

SQL Problem using SQL Server 2000

Assume we have loaded a flat file with patient diagnosis data into a table called “Data”. The table structure is:

Create table Data (
Firstname varchar(50),
Lastname varchar(50),
Date_of_birth datetime,
Medical_record_number varchar(20),
Diagnosis_date datetime,
Diagnosis_code varchar(20))


The data in the flat file looks like this:

'jane','jones','2/2/2001','MRN-11111','3/3/2009','diabetes'
'jane','jones','2/2/2001','MRN-11111','1/3/2009','asthma'
'jane','jones','5/5/1975','MRN-88888','2/17/2009','flu'
'tom','smith','4/12/2002','MRN-22222','3/3/2009','diabetes'
'tom','smith','4/12/2002','MRN-33333','1/3/2009','asthma'
'tom','smith','4/12/2002','MRN-33333','2/7/2009','asthma'
'jack','thomas','8/10/1991','MRN-44444','3/7/2009','asthma'




You can assume that no two patients have the same firstname, lastname, and date of birth combination.

The problem is this: Tom Smith has 2 different medical record numbers. Write a query that would always show all the patients who are like Tom Smith – patients with more than one medical record number.

This problem has many solutions, but if you know SQL, you should be able to find one that uses a single query with no subqueries.


Gary