Left join problem - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Left join problem

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-10 : 19:48:43

I have a query like below


select qms.personal_ref_id,qms.qms_hgb, lr.time_hgb, lr.dt from

(
         select * from openquery
            ( oracle_ra,
            '
            SELECT
             pp.personal_ref_id,lr.lab_result_date,lr.lab_result_reading * lr.lab_item_num as qms_hgb
            FROM  ra.LAB_RESULT LR, ra.PATIENT_PERSONAL PP, ra.UNIT
            WHERE PP.personal_num = LR.personal_num
            AND unit.unit_num = LR.unit_num
            AND  LR.LAB_RESULT_DATE BETWEEN ''01-sep-2006'' and ''30-sep-2006''
            AND  (  LR.LAB_ITEM_NUM = 3  )      --      1=hct  3=hgb
            AND personal_ref_id = ''276340''
            '
           ))qms

 left join (
            SELECT
            lr.chart_number,
            LR.LAB_DATE as DT,
            MAX(case when LR.TEST_CODE in ('HGB', 'HEMOGLOBIN') then num_result *3  end) as time_HGB

            FROM labrec_view LR
            LEFT JOIN ODS_PATIENT P ON LR.CHART_NUMBER = P.PATIENT_ID AND LR.PATIENT_NO = P.PATIENT_NO

            WHERE LR.LAB_DATE BETWEEN '9/1/06'       AND '9/30/06'
            AND LR.TEST_CODE IN('HGB', 'HEMOGLOBIN')
            and lr.chart_number = '276340'

            GROUP BY
            lr.chart_number,
            LR.LAB_DATE
               )lr on  qms.personal_ref_id  = lr.chart_number AND  QMS.qms_hgb = lr.time_HGB


Open query return : qms

276340     2006-09-01 00:00:00.000     35.4
276340     2006-09-02 00:00:00.000     35.4
276340     2006-09-04 00:00:00.000     35.4
276340     2006-09-06 00:00:00.000     35.4
276340     2006-09-08 00:00:00.000     35.4
276340     2006-09-11 00:00:00.000     35.4
276340     2006-09-13 00:00:00.000     35.4
276340     2006-09-15 00:00:00.000     35.4
276340     2006-09-18 00:00:00.000     35.4
276340     2006-09-20 00:00:00.000     35.4
276340     2006-09-22 00:00:00.000     35.4
276340     2006-09-25 00:00:00.000     35.4

and another sub query return lr


276340     2006-09-01 00:00:00.000     32.40
276340     2006-09-06 00:00:00.000     32.10
276340     2006-09-11 00:00:00.000     32.10
276340     2006-09-18 00:00:00.000     35.40
276340     2006-09-25 00:00:00.000     40.50

If I left join QMS to lr based on qms.personal_ref_id = lr.chart_number AND QMS.qms_hgb = lr.time_HGB
, I get


276340     2006-09-01 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-02 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-04 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-06 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-08 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-11 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-13 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-15 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-18 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-20 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-22 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-25 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000

i am not sure Why I get the above result even though I am doing a left join .. I need to get the result look like...How can I do this?


276340     2006-09-01 00:00:00.000     35.4     null       null
276340     2006-09-02 00:00:00.000     35.4     null       null
276340     2006-09-04 00:00:00.000     35.4     null       null
276340     2006-09-06 00:00:00.000     35.4     null       null
276340     2006-09-08 00:00:00.000     35.4     null       null
276340     2006-09-11 00:00:00.000     35.4     null       null
276340     2006-09-13 00:00:00.000     35.4     null       null
276340     2006-09-15 00:00:00.000     35.4     null       null
276340     2006-09-18 00:00:00.000     35.4     35.40     2006-09-18 00:00:00.000
276340     2006-09-20 00:00:00.000     35.4     null       null
276340     2006-09-22 00:00:00.000     35.4     null       null
276340     2006-09-25 00:00:00.000     35.4     null       null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 01:40:22

LEFT JOIN by date too!

)lr on qms.personal_ref_id = lr.chart_number AND QMS.qms_hgb = lr.time_HGB AND QMS.lab_result_date = lr.dt

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources