August 13, 2011 at 8:15 am
Hi,
I inherited an MDF file with more than 40 tables representing patient lab testing data that did not have corresponding database documentation. I have been asked to write a query that puts data from several tables in the same place. I had to take a hardcopy report of some of the lab tests and used that to sleuth out which table goes with the various data points. For example, I found name and address on one table, height and weight on another, and (what appears to be) the test measurements on another. The field names have nothing to do with the actual data contained in them, so it is not intuitive (e.g. height has a generic name and is not called "height"). There are many other tables that are mostly number/id fields that appear to have something to with linking the tables that I have identified together.
It would be great if the primary key ID in the Patient table was the same for all tables and I could just join the tables that way, but it isn't. The patient table links to some others, the measurement table links to some others, but I can't figure out how to get the group of tables that go with the patient table to link to the group of tables that link to the measurement tables.
Query editor was helpful in that it identified primary/foreign keys for me. So, basically, if I join all of the tables in the database, I will eventually get one patient record per row that reflects the hardcopy test report. However, it seems that whenever I add a fourth table to the join statement, it runs the query but does not return data.
In this example, when I ran this query before adding the "Paragraph" table, it returned data. When I added Paragraph (which I know does contain data), the query returned no data.
Any suggestions/advice would be greatly appreciated.
Thanks!
Danielle
SELECT MEASPARAMETER.PARAMETER_ID, MEASPARAMETER.SUBMEAS_Id, MEASPARAMETER.PARADES_Id, MEASPARAMETER.PARA_Index,
MEASPARAMETER.PARA_Datatype, MEASPARAMETER.PARA_Value, MEASPARAMETER.PARA_PredValue, MEASPARAMETER.PARA_Status,
MEASPARAMETER.PARA_Deleted, MEASPARAMETER.PARA_GenDate, MEASPARAMETER.PARA_ModDate, MEASPARAMETER.PARA_LocalGenDate,
MEASPARAMETER.PARA_LocalModDate, MEASPARAMETER.PARA_GenUser, MEASPARAMETER.PARA_ModUser,
MEASPARAMETER.PARAMETER_ID AS Expr1, MEASPARAMETER.PARAMETER_ID AS Expr2, MEASPARAMETER.SUBMEAS_Id AS Expr3,
MEASPARAMETER.PARADES_Id AS Expr4, MEASPARAMETER.PARA_Index AS Expr5, MEASPARAMETER.PARA_Datatype AS Expr6,
MEASPARAMETER.PARA_Value AS Expr7, MEASPARAMETER.PARA_PredValue AS Expr8, MEASPARAMETER.PARA_Status AS Expr9,
MEASPARAMETER.PARA_Deleted AS Expr10, MEASPARAMETER.PARA_GenDate AS Expr11, MEASPARAMETER.PARA_ModDate AS Expr12,
MEASPARAMETER.PARA_LocalGenDate AS Expr13, MEASPARAMETER.PARA_LocalModDate AS Expr14, MEASPARAMETER.PARA_GenUser AS Expr15,
MEASPARAMETER.PARA_ModUser AS Expr16, PARAGRAPH.DOCP_Text
FROM PARA_DESC INNER JOIN
PARA2MEASURE ON PARA_DESC.PARADES_Id = PARA2MEASURE.PARADES_Id INNER JOIN
MEASPARAMETER ON PARA_DESC.PARADES_Id = MEASPARAMETER.PARADES_Id INNER JOIN
SUBMEASURE ON MEASPARAMETER.SUBMEAS_Id = SUBMEASURE.SUBMEAS_Id INNER JOIN
SYMBOLDESC ON PARA_DESC.SYM_Nr = SYMBOLDESC.SYM_Nr INNER JOIN
SYMBOLITEM ON SYMBOLDESC.SYM_Nr = SYMBOLITEM.SYM_Nr INNER JOIN
PARAGRAPH ON SYMBOLDESC.SYM_Nr = PARAGRAPH.SYM_Nr
August 13, 2011 at 8:37 am
If you turn that last inner join you added to a left join do you go back to 1 row being return? If so it means that you have the wrong key matching or that there's no data matching (could be bad data too).
For reverse engineering you can use profiler. Open a trace, filter on your login or pc. Then start the app the reads that data and see how the joins are built.
August 13, 2011 at 8:53 am
Thanks! I'll try profiler.
When I add left, I get results this time, but all of the fields that result are NULL.
August 13, 2011 at 8:59 am
See the ids in both tables. Maybe you got the joins wrong or there's no data.
August 13, 2011 at 10:27 am
Profiler is a great way to help. The other thing I'd suggest is that you start building documentation as you go. Note what you think tables are used for and what the IDs and important columns mean. It won't be 100% correct, but it will get you moving in the right direction.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply