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
huyenbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You aren't using LEFT JOIN but RIGHT JOIN.
Anyway, did you try with INNER JOIN to check if returns what you want?
huyenbAuthor Commented:
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
PatHartmanCommented:
For a Left Join, you will get all the rows in tblA plus any matching rows in tblB.  If tblB has more than one row that matches tblA, the query will return more rows than exist in tblA.  So, if you Left join Customer to Orders, you will end up with a list of customers whether or not they have placed any orders and you will see some customers listed more than once if they have placed multiple orders.

If you want to return only one row for each row in tblA, you need to aggregate the data selected from tblB using first, last, sum, avg, count, min, max, etc.  If you want the specific values from tblB, then you will get multiple rows.

So, using our Customer/Orders example, if all you wanted was a count of orders, you would not select ANY columns from Orders but you would use a Count(*) aggregate.  If you wanted to find the total value of the orders, you would use Sum(OrderAmt), If you wanted to see shipping notes, you could use Group By and that would give you the distinct shipping notes but you are still likely to get multiple rows.  If you only wanted an example, then you could use First/Last/Min/Max to pick a single value.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huyenbAuthor Commented:
Thank you PatHartman,

That's very easy to understand!
huyenbAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.