sql - Unexpected NULLs in JOIN query -

i using 3 tables in query: 2 inner join , 3rd left join.

however, getting nullrecords while left join condition executing. there coming purchase line(pl_ table)`which should not case.

can 1 recommend change in query replace null values in record actual values?

sorry, not find option attach sample table data.

the query

select     pih.[po number],      pih.[pre-assigned no_] [invoice no],     pil.[document no_],     pil.[description] [reason discrepency],     pil.[line no_],     pl.[no_] [item no],     pl.[vendor item no_],     pl.[order date],     pil.[posting date],     pil.[expected receipt date],     pih.[notes] [header notes],     pil.[no_] [g/l account no],     pih.[buy-from vendor no_],     pih.[buy-from vendor name],     pil.quantity [inv qty invoiceline],      pil.[amount including vat] [inv value invoiceline],      pl.quantity [po quantity purchaseline],      pl.[quantity received] [received qty purchaseline],     pl.[quantity invoiced] [invoiced qty purchaseline] ,     pl.[amount including vat] [po value purchaseline]      [purch_ inv_ line] pil     inner join purch_ inv_ header] pih         on pil.[document no_] = pih.[no_]     left join [purchase line] pl          on pih.[po number]=pl.[document no_] , pl.[line no_]=pil.[line no_]      pil.[document no_] in     (         select distinct pil.[document no_]                purch_ inv_ line] pil          pil.[no_] in ('700xxx','700xxx','17xxxxx') , pil.[posting date] >=getdate()-7     )     , pil.[type]='1' 

there might 2 reasons why getting null values:

• values of columns in [purchase line] table null

• there no matching rows in [purchase line] table rows null. how left join works

if want matching rows, change left join inner join - result set not contain rows [purch_ inv_ line] , [purch_ inv_ header] there not matching [purchase line] rows.

if want give default values null values can using isnull:

isnull(pl.[amount including vat], 0) [po value purchaseline]