I get empty fields, even though there is data there

97 views Asked by At
SELECT 
 [Structure]=mpc.StructureName
 ,[Type]=CASE pcbor.TypeId 
             WHEN '0080' THEN 'type1'
             WHEN '1845' THEN 'type2'
             WHEN '0125' THEN 'type3'
             Else 'N/A' END
 ,Type_De=CASE pcde.TypeId WHEN '0040' THEN 'Type99' Else 'N/A' END 
 ,Type_Ee=CASE pcee.TypeId WHEN '1525' THEN 'Type98'
                           WHEN '1530' THEN 'Type97' Else 'N/A' END
 ,Type_Lt=CASE pclt.TypeId WHEN '1525' THEN 'Type96'
                           WHEN '1530' THEN 'Type95' Else 'N/A' END

FROM [static].[MPC] with (nolock)
     JOIN [static].[MPCCenter] mpcpc with (nolock) ON mpcpc.MPCId = mpc.MPCId
     JOIN [static].[ProCenter] pc with (nolock) ON pc.ProCenterId = mpcpc.ProCenterId
    LEFT JOIN [static].TypeBor pcbor with (nolock) ON pcbor.ProCenterId = pc.ProCenterId
    LEFT JOIN [static].Type_De pcde with (nolock) ON pcde.ProCenterId = pc.ProCenterId
    LEFT JOIN [static].Type_Ee pcee with (nolock) ON pcee.ProCenterId = pc.ProCenterId
    LEFT JOIN [static].Type_Lt pclt with (nolock) ON pclt.ProCenterId = pc.ProCenterId

Is there anything wrong with my code, because there is data in all the tables but all I get in my result is data from the first table(pcbor.typeid) the rest is N/A ?

1

There are 1 answers

0
Mihir Shah On

You are applying left join and you are also not checking Isnull as it may possible to give null values due to left join and 'N/A' will be there, you are left joining 3 different tables which returns different rows for each ProcenterId, so 'N/A' would be there

see the below attach image (what i understood from your scenario).

enter image description here