Post reply

Help with SQL Query - How to select two records as one because of different results stored in field.

  • January 22, 2008 at 11:47 am

    #61490

    I need help with a SQL query that involves test results. I need to create a report for a person who has had a specific blood test. The sample type (such as capillary or venous) is stored in a Result field. The result of the test is also stored (as a separate record for the same person but with a different code) in the same Result field. I need to create a report that shows the test result, as well as the sample type, in the same record. The only thing I've been able to do is select two rows - one with the result and one with the sample type. I need a one-row result that includes both.

    Can anyone help? I've been all over this and can't figure a way to do it except select the test result and store in a temporary table and then select the sample type and store in the same row as a different field in the temporary table - then create a report from this temporary table. There should be a simple way, but this is escaping me.

    Can anyone help?

  • January 22, 2008 at 11:53 am

    #770597

    Is some form of a self join not workable? You join the table to itself. In this case - one instance returns the sample, the other returns the test result.

    See if this jogs anything loose:

    select sample.*, testresult.*

    from MyLabResults sample

    inner join MyLabResults TestResult

    on sample.PatientID=TestResult.PatientID

    and sample.TestTypeID=TestResult.TestTypeID --amd whatever else you need to get a match

    WHERE sample.SampleType='Sample'

    and testresult.SampleType='Result'

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 22, 2008 at 12:43 pm

    #770618

    Thanks! That helped. I tried self-joining on the Result table, but apparently just didn't get it quite right. I've got it now.

    Sincerely appreciate the help!!!

  • January 22, 2008 at 1:59 pm

    #770653

    Glad to hear it helped! Thanks for the feedback.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply