asked on

LEFT OUTER JOIN returns more records

Hi,

I hope that anyone can help me with this problem because I've tried different ways. I have a lookup here, the main source table D has let's say 30 records, table P has 40. That's why I put Left outer join to return just record that match from left table. but it still more than 30. I've tried distinct/group by but it doesn't seem right. Is there any other way I can try to get the exact match?

Thank yoU!

SELECT D.PATIENT_CLINIC_NUMBER, P.PATIENT_CLINIC_NUMBER

FROM
(
select ptName,CASE WHEN (SITEID in (123456,654321))
AND LENGTH(RTRIM(TRANSLATE(COALESCE(PTID,''), '*', ' 0123456789'))) = 0
and LENGTH(TRIM(COALESCE(PTID,''))) > 7
THEN CAST(SUBSTR(COALESCE(PTID,''), 1,7) AS BIGINT)
ELSE '0' END AS PATIENT_CLINIC_NUMBER

from (SELECT
pu.ptName, pu.SiteID, pu.PTID, pu.TXDATETIME
,ROW_NUMBER() OVER (PARTITION BY ptid ORDER BY TxDateTime DESC ) as RowNum

FROM STAGE.STG1_PYXIS_DATA pu
) t1
WHERE t1.RowNum = 1) D
RIGHT JOIN EDTWH.DIM_PATIENT P
ON D.PATIENT_CLINIC_NUMBER = P.PATIENT_CLINIC_NUMBER
AND D.PTNAME = P.PATIENT_FULL_NAME

Vitor Montalvão

You aren't using LEFT JOIN but RIGHT JOIN.
Anyway, did you try with INNER JOIN to check if returns what you want?

huyenb

ASKER

Hi Vitor,

Thank you for pointing that out, after failing with LEFT OUTER JOIN, I tried RIGHT JOIN (I know it sounds dumb even though I can tell the result)

As I mentioned above, it's a look up query so the first part of the FROM is the Source query, and the second part contains the ID that I want to look up. With INNER JOIN, it returned way more than that

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

huyenb

ASKER

Thank you PatHartman,

That's very easy to understand!

huyenb

ASKER

Hi all,

Just want to update the solution..using Count (Distinct ...) also helped with this problem

SELECT COUNT(DISTINCT CASE WHEN COALESCE(P.PATIENT_FULL_NAME,'') = '' Then 0
WHEN SUBSTR(UPPER(D.PTNAME),1,4) = SUBSTR(UPPER(P.PATIENT_FULL_NAME),1,4)
Then P.PATIENT_CLINIC_NUMBER
Else -1 END) AS PATIENT_CLINIC_NUMBER_COUNT

FROM

(
select ptName,CASE WHEN (SITEID in (1002360,1647000))
AND LENGTH(RTRIM(TRANSLATE(COALESCE(PTID,''), '*', ' 0123456789'))) = 0
and LENGTH(TRIM(COALESCE(PTID,''))) > 7
THEN CAST(SUBSTR(COALESCE(PTID,''), 1,7) AS BIGINT)
ELSE '0' END AS PATIENT_CLINIC_NUMBER

from (SELECT
pu.ptName, pu.SiteID, pu.PTID, pu.TXDATETIME
,ROW_NUMBER() OVER (PARTITION BY ptid ORDER BY TxDateTime DESC ) as RowNum

FROM STAGE.STG1_PYXIS_DATA pu
) t1
WHERE t1.RowNum = 1) D
LEFT OUTER JOIN EDTWH.DIM_PATIENT P
ON D.PATIENT_CLINIC_NUMBER = P.PATIENT_CLINIC_NUMBER