Left Join Problem

[sasuser_sk]

Hello - I have 800 rows in my left table (no duplicates). When I Left join using code below it only extracts 250 rows and those are the ones that matched to left table. Can I please pick your brains to find error in my code? Thanks a lot!

PROC SQL;
Create table WORK.ABC AS
Select Distinct a.*, b.Name, b.Trans_date, b.Client_Add Date, b.Client_Type

From WORK.CUSTOMERS a
LEFT JOIN SERVRE.TRANSACTIONS b ON ((a.ACCT_NO = b.ACCT_NO) and (a.ID_NO = b.ID_NO))
WHERE (b.Name LIKE 'JE%')
          AND ((DATEPART(b.Trans_Date) >= '13JUL2020'D)
         AND (DATEPART(b.Trans_Date) <= '18JAN2021'D))

        AND (b.Client_Type = 'Continuously')

order by a.ACCT_NO,
              a.ID_NO;
QUIT;

[PeterClemmensen]

Loose the distinct keyword.

[sasuser_sk]

It didn't help rather created the duplicate rows.

[Tom]

WHERE is applied the results of the LEFT JOIN.

Try moving those extra restrictions into the ON condition instead.

[sasuser_sk]

Thanks for your response Tom.

[novinosrin · Accepted solution]

HI @sasuser_sk  Can you try replacing WHERE with AND

LEFT JOIN SERVRE.TRANSACTIONS b ON ((a.ACCT_NO = b.ACCT_NO) and (a.ID_NO = b.ID_NO))
AND (b.Name LIKE 'JE%') 
          AND ((DATEPART(b.Trans_Date) >= '13JUL2020'D)
         AND (DATEPART(b.Trans_Date) <= '18JAN2021'D))

        AND (b.Client_Type = 'Continuously')

[sasuser_sk]

BINGOOOOOOO!!!!! Awesome.

Thank you so much!

[Reeza]

What makes you think you have an error? If you're getting an error in the log, please post it. If it's because you're expecting 800 records, that's not quite right.
The WHERE clause is filtering out data so you're losing those records. Note that character comparisons are case sensitive so if you have Je it will not be included in your output.

To determine how your records are being filtered you can play around with the WHERE clause to see which one is limiting the records.

[sasuser_sk]

Playing around with WHERE worked fine and I got all the records from left table back along with the data that matched with them.  Thx!