Join with a Null value

• xxxxxxxx

  Old Hand

  Points: 339

  More actions

  February 3, 2015 at 12:37 pm

  #150325

  I have the following two tables in MS SQL 2005. I am trying to return all rows with concatenation of the name, Street 1, Street2, City, and State. However, I get the following returned:

  VNos 3780600, 2844660, 2332270 => returned with the correct concatenation in column 0

  VNos 2285940,2809700 => returned but with blank concatenation in column 0 (only the pipe delimiters are returned) and no data for name, street1, street2, etc

  What am I doing incorrectly which is preventing me from returning all rows with the correct concatenation?

  And, how do I get this work using the easiest and most straightforward approach?

  TABLE 1

  VNo,Vch.CID, Vch.OCID,

  2285940, {null},170

  2809700, {null},922

  3780600, 273,273

  2844660, 252,252

  2332270, 577, {null}

  TABLE 2

  CRS.CID,Crs.NAME,Crs.Street1,Crs.Street2,Crs.CITY,Crs.STATE

  273, AAA,5 St1,{null},NYC,NY

  252,BBB,20 St1,{null},NYC,NY

  922,CCC,6 St1,{null},NYC,NY

  170,DDD,26 St1,{null},NYC, NY

  577,EEE,7 St1,PO Box S2,NYC,NY

  SET CONCAT_NULL_YIELDS_NULL OFF

  /*

  For MS SQL Server 2012 and BEFORE When SET CONCAT_NULL_YIELDS_NULL is ON,

  concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL.

  When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself

  (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

  However, in SQL Server 2012 and AFTER the behavior of the ‘+’ operator remains the same but the behavior of

  function CONCAT is bit different and it totally ignores this value.

  */

  Select Distinct

  Case

  When ISNULL(Vch.CID,0) = ISNULL(PM.Vch.OCID,0)

  Then Crs.Name +' | '+ Crs.Street1 +' | '+ Crs.Street2 +' | '+

  Crs.City +' '+ PM.Crs.State

  When ISNULL(Vch.CID,0) != ISNULL(Vch.OCID,0)

  Then Crs.Name +' | '+ Crs.Street1 +' | '+ Crs.Street2 +' | '+

  Crs.City +' | '+ Crs.State

  End As '0',

  Vch.VNo,

  Vch.CID AS 'CID',

  Vch.OCID AS 'OCID',

  From Vch

  Full Outer Join

  Crs on Vch.CID = Crs.CID

  Where

  Vch.VNo In(2285940,2809700, 3780600,2844660, 2332270)