Best way of acquiring information from several database tables

Question

I have a medical database that keeps different types of data on patients: examinations, lab results, x-rays... each type of record exists in a separate table. I need to present this data on one table to show the patient's history with a particular clinic.

My question: what is the best way to do it? Should I do a SELECT from each table where the patient ID matches, order them by date, and then keep them in some artificial list-like structure (ordered by date)? Or is there a better way of doing this?

I'm using WPF and SQL Server 2008 for this app.

Answer 1

Use a JOIN to get data from several tables.

Answer 2

If this is something you're going to be doing often, I'd be tempted to create a denormalized view on all of patient data (join the appropriate tables) and index the appropriate column(s) in the view. Then use the appropriate method (stored procedure, etc) to retrieve the data for a passed-in patientID.

Answer 3

You can use a join (can't remember which type exactly) to get all the records from each table for a specific patient. The way this works depends on your database design.

Answer 4

As others have said, JOIN is the way you'd normally do this. However, if there are multiple rows in one table for a patient then there's a chance you'll get data in some columns repeated across multiple rows, which often you don't want. In that case it's sometimes easier to use UNION or UNION ALL.

Let's say you have two tables, examinations and xrays, each with a PatientID, a Date and some extra details. You could combine them like this:

SELECT PatientID, ExamDate [Date], ExamResults [Details]
FROM examinations
WHERE PatientID = @patient

UNION ALL

SELECT PatientID, XrayDate [Date], XrayComments [Details]
FROM xrays
WHERE PatientID = @patient

Now you have one big result set with PatientID, Date and Details columns. I've found this handy for "merging" multiple tables with similar, but not identical, data.

Answer 5

I'd do it with separate SELECT statements, since a simple JOIN probably won't do due to the fact that some tables might have more than 1 row for the patient.

So I would retrieve multiple result-sets in a simple DataSet, add a DalaRelation, cache the object and query it down the line (by date, by exam type, subsets, ...)

The main point is that you have all the data handy, even cached if needed, in a structure which is easily queried and filtered.