INNER JOIN & IS NOT NULL probs

[MATTXtwo]

I have this store procedure to select data from table with join like this...

SELECT     tblPeribadi.Personel_No, tblPeribadi.Nama,tblCompany.Keterangan as Company_Code, tblPeribadi.Jawatan, tblPeribadi.Taraf_Jawatan, 
                      tblGroup.Keterangan AS Kumpulan,tblPeribadi.Gred,tblBusiness_Area.Keterangan AS Business_Area,tblCost_Center.Keterangan AS Kod_Pusat_Kos,  tblPeribadi.IC_Baru, tblPeribadi.IC_Lama, 
                      tblPeribadi.Jantina, tblPeribadi.BumiStatus, 
	CAST(DAY(tblPeribadi.Tarikh_Lahir) AS VARCHAR(2)) + ' ' + DATENAME(MM, tblPeribadi.Tarikh_Lahir) + ' ' + CAST(YEAR(tblPeribadi.Tarikh_Lahir) AS VARCHAR(4)) AS [Tarikh_Lahir],
					  tblPeribadi.Tempat_Lahir,TblCountry.CDesc as Negara_Kelahiran,  tblstate.Statedesc as Negeri_Kelahiran, 
                      tblMaritulStatus.MarStat as Taraf_Perkahwinan, 
	CAST(DAY(tblPeribadi.Tarikh_Perkahwinan) AS VARCHAR(2)) + ' ' + DATENAME(MM,tblPeribadi.Tarikh_Perkahwinan) + ' ' + CAST(YEAR(tblPeribadi.Tarikh_Perkahwinan) AS VARCHAR(4)) AS [Tarikh_Perkahwinan],
					  tblreligious.Rel_Denomination as Agama, tblEthnic.EthnicDesc as Bangsa, tblPeribadi.No_EPF, tblPeribadi.No_Socso, 
                      tblPeribadi.No_Tax, tblPeribadi.Email  
FROM         tblPeribadi INNER JOIN
                      tblCompany ON tblPeribadi.Company_Code=tblCompany.Company_Code INNER JOIN
                      tblGroup ON tblPeribadi.Kumpulan = tblGroup.Kumpulan INNER JOIN
                      tblBusiness_Area ON tblPeribadi.Business_Area = tblBusiness_Area.Business_Area INNER JOIN
                      tblCost_Center ON tblPeribadi.Kod_Pusat_kos = tblCost_Center.Kod_Pusat_Kos INNER JOIN
                      TblCountry ON tblPeribadi.Negara_Kelahiran = TblCountry.CShort inner join 
tblstate on tblPeribadi.Negeri_Kelahiran=tblstate.region inner join 
tblMaritulStatus on tblPeribadi.Taraf_Perkahwinan=tblMaritulStatus.MarCode inner join
tblreligious on tblPeribadi.Agama=tblreligious.relcode inner join 
tblethnic on tblPeribadi.Bangsa=tblEthnic.EthnicCode
where tblperibadi.personel_no=@Personel_No

which some of data from table Peribadi =Null value might effect the inner join...but the thing is I want to get this data event if it's null
like it was only select for table peribadi

[ck9663]

You have multiple JOINS that uses different join keys. You have to use LEFT JOIN on those that you want the NULL values to be returned.

Say you need to return all those with tblPeribadi.Kum pulan = NULL use this

LEFT JOIN tblGroup ON tblPeribadi.Kumpulan = tblGroup.Kumpulan

only for the tblGroup and use INNER JOIN for the rest. If you have multiple columns that are NULL and you want those rows returned, use LEFT JOIN in all those column JOINS.

-- CK

[MATTXtwo]

Thanks I try my best