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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help please with simple query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 10/09/2012 :  16:25:44  Show Profile  Reply with Quote
hello, I have been asked to answer this question and have no idea. can someone help me? I am told it is easy.
Assume we have loaded a flat file with patient diagnosis data into a table called “patientdata”. The table structure is:

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

The data in the flat file looks like this:
First Last date_of_birth Med Rec No Diag Loc Diag Date Diag Code
------- ------ ------------ ---------- ------------- ---------- ---------
'jane','jones','2/2/2001', 'MRN-11111','Mayo Clinic', '3/3/2009','diabetes'
'jane','jones','2/2/2001', 'MRN-11111','Mayo Clinic', '1/3/2009','asthma'
'jane','jones','5/5/1975', 'MRN-88888','Lahey Clinic','2/17/2009','flu'
'tom','smith','4/12/2002', 'MRN-22222','Lahey Clinic','3/3/2009','diabetes'
'tom','smith','4/12/2002', 'MRN-33333','Mayo Clinic', '1/3/2009','asthma'
'tom','smith','4/12/2002', 'MRN-33333','Mayo Clinic', '2/7/2009','asthma'
'jack','thomas','8/10/1991','MRN-44444','Mayo Clinic', '3/7/2009','asthma'
'jack','thomas','8/10/1991','MRN-44444','Lahey Clinic','3/16/2009','flu'

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

Write a query that shows which of these patients were diagnosed in more than one location.

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.

The Chadinator

1974 Posts

Posted - 10/09/2012 :  19:13:19  Show Profile  Visit chadmat's Homepage  Reply with Quote

What do you have so far? Where are you running into problems?

Go to Top of Page

Starting Member

2 Posts

Posted - 10/09/2012 :  19:56:04  Show Profile  Reply with Quote
No not homework. Im trying for a job and they threw this question in on their online submission form. I have not used sql..yes Im old..50,,,and Im just plain in the weeds. i've gotten an online course and Im trying to wade through in the hopes that I can at least understand what is being asked of me.
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 10/09/2012 :  21:25:32  Show Profile  Reply with Quote
look up for GROUP BY and COUNT in google and make a try. If you face any issues post and we will help you out.

Otherwise you wont be learning to do it yourself

SQL Server MVP

Go to Top of Page

Flowing Fount of Yak Knowledge

2242 Posts

Posted - 10/10/2012 :  00:34:56  Show Profile  Reply with Quote
Check this link... Same query

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000