Left Join Returning Duplicate Rows When There Is Match Between Two Tables And Match Is Not Null

I am trying to join two table using left join on following tables Account Table Zone ACC_NUM Profile Status INT 123456 11 Active DOM 246810 12 Active IN

Solution 1:

Your code is perfectly valid:

CREATETABLE #Account(
   Zone    VARCHAR(30) NOTNULL 
  ,ACC_NUM INTEGERNOTNULL
  ,Profile INTEGERNOTNULL
  ,Status  VARCHAR(80) NOTNULL
);
INSERTINTO #Account(Zone,ACC_NUM,Profile,Status) VALUES ('INT',123456,11,'Active');
INSERTINTO #Account(Zone,ACC_NUM,Profile,Status) VALUES ('DOM',246810,12,'Active');
INSERTINTO #Account(Zone,ACC_NUM,Profile,Status) VALUES ('INT',135791,12,'Inactive');


CREATETABLE #Meter(
   Acc_Num INTEGERNOTNULL 
  ,Meter   VARCHAR(60) 
);
INSERTINTO #Meter(Acc_Num,Meter) VALUES (123456,'156894');
INSERTINTO #Meter(Acc_Num,Meter) VALUES (135791,NULL);


Select a.Zone, a.Acc_Num, a.Status, m.Meter 
From #Account a
leftjoin #Meter m on m.Acc_Num=a.Acc_Num;

LiveDemo

The problem is probably your data in Meter table. I guess you have additional row with 123456 NULL value.

If you want only one value per group you can use:

Select a.Zone, a.Acc_Num, a.Status, m.Meter 
From #Account a 
left join (SELECT Acc_Num, MIN(Meter) AS Meter 
           FROM #Meter 
           GROUPBY Acc_Num) AS m on m.Acc_Num=a.Acc_Num

LiveDemo2

Solution 2:

Your query is about the best way to do it. However, to answer your question how do I handle if just in case there is a NULL to get the expected results?, just add a NULL check to your join condition:

Select a.Zone, a.Acc_Num, a.Status, m.Meter 
From #Account a
leftjoin #Meter m onNOT m.Meter ISNULLAND m.Acc_Num = a.Acc_Num;

Here is a fiddle.