Wondering if someone can help me write a SQL query. I have a table that contains a bunch of patient data. Each patient has a medical record number assigned to a study. Each study has a unique study reference number which is the primary key. Long story short, I need to write a query that returns one line for each unique medical record number with some other data. Basically like this.
SELECT patient_id, patient_name, DOB from table
this is where I get stuck, I need to somehow incorporate some syntax where I only get one entry per unique or distinct patient id. Hopefully that makes sense.
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Answers (8)
0
Votes
Is the date in multiple tables? Single table?
Are you looking strictly for distinct rows for patient ids, names and DOB? Or are you looking for something else?
13th Jun
Replies
It's a single table. I only want one result per Patient ID.
jimmy-jam
13th Jun
0
Votes
Have you tried
Select distinct id, name, DOB from table
13th Jun
0
Votes
This?
SELECT patient_id, patient_name, DOB
FROM table
GROUP BY patient_id
Is that what you were looking for?
FROM table
GROUP BY patient_id
Is that what you were looking for?
13th Jun
Replies
That query wouldn't give me only one line per patient_id.
jimmy-jam
14th Jun
2
Votes
Patient Privacy Act
I have no tech advise, but be careful. You are, without the proper permissions, dancing dangerously close to a violation of HIPAA's Patient Privacy rules. The joint commission could potentially see some of this as a violation. Check with your HIM department, or Enterprise/EPIC departments.
13th Jun
Replies
It could just be his assignment for school! However, I am very happy that you have identified this. Health Care Info System workers need to be on Top the Biz Analytic path.
mychitterman@...
13th Jun
I appreciate your concern and they are duly noted. I work for a medical imaging company and am well aware of HIPAA and my responsibility to protect the privacy of patients. I would never dream of posting and Personally Identifiable Information on a public forum.
jimmy-jam
14th Jun
0
Votes
Multlple Entries
for a customer ID in a table(s) will return multiple records, which it should do, in a pure SQL query. What you need to do is post process this returned data to display in your user interface (front end).
EG Table "Data"
Cust_ID Cust_Name Cust_DOB Cust_MisInfo
1 A Name 5/5/AYear 2 Yr Contract
1 A Name 5/5/AYear Terminated
2 B Name 4/4/AYear 1 Yr Contract
SQL Statement "SELECT Cust_ID,Cust_Name,Cust_DOB,Cust_MisInfo FROM Data WHERE Cust_ID = 1;"
The statement above will return two records as it appears in the table(s).
EG Table "Data"
Cust_ID Cust_Name Cust_DOB Cust_MisInfo
1 A Name 5/5/AYear 2 Yr Contract
1 A Name 5/5/AYear Terminated
2 B Name 4/4/AYear 1 Yr Contract
SQL Statement "SELECT Cust_ID,Cust_Name,Cust_DOB,Cust_MisInfo FROM Data WHERE Cust_ID = 1;"
The statement above will return two records as it appears in the table(s).
14th Jun
0
Votes
One patient participating in 2 studies
will always output 2 lines in a query involving patient and study data. If you want to list that in a (HTML, f.ex.) table you have to execute one query to get all patients and then for each patient you have to SELECT * FROM Studies WHERE Studies.Cust_ID = Patients.Cust_ID
But still consider bsongy's comment regarding joining patient data with study results. You may quite fast finish with a big fine whenever (and that's most of the time) the patient's (study) data are protected and have to be kept in a way that the study result remains absolutely anonymous.
But still consider bsongy's comment regarding joining patient data with study results. You may quite fast finish with a big fine whenever (and that's most of the time) the patient's (study) data are protected and have to be kept in a way that the study result remains absolutely anonymous.
14th Jun
Replies
Thanks, as I stated to bsongy. This is part of a requested report from a customer of mine that happens to be a hospital. I work for the vendor that supplied the equipment. The customer has merely requested that I do some data mining for them. The requesting department already has full access to the data legally as do I. I do appreciate you guys bringing it up though in the off chance that someone else may stumble across this thread. It never hurts to err on the side of caution.
jimmy-jam
14th Jun
0
Votes
well unless...
you make a weird pivot queries that puts all study data on the same line as the patient data.
Not recommended
Not recommended
14th Jun
0
Votes
The solution is simple
Hello,
I use to do data analysis for a large medical research organisation. I assume the database if fully normalised. So you just need to query the patient table for the details and then in the from part of the query use an "exists" or "in" sub-query to only show those patients with a valid medical record number in the study table. Without more details I can't help you further but you may need to use something like a max() value in the sub-query if the sub query returns more than one row. Do not try to join the tables as this will cause problems. Hope this helps.
I use to do data analysis for a large medical research organisation. I assume the database if fully normalised. So you just need to query the patient table for the details and then in the from part of the query use an "exists" or "in" sub-query to only show those patients with a valid medical record number in the study table. Without more details I can't help you further but you may need to use something like a max() value in the sub-query if the sub query returns more than one row. Do not try to join the tables as this will cause problems. Hope this helps.
14th Jun
