By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

INNER JOIN & IS NOT NULL probs

P: 83
I have this store procedure to select data from table with join like this...
Expand|Select|Wrap|Line Numbers
  1. SELECT     tblPeribadi.Personel_No, tblPeribadi.Nama,tblCompany.Keterangan as Company_Code, tblPeribadi.Jawatan, tblPeribadi.Taraf_Jawatan, 
  2.                       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, 
  3.                       tblPeribadi.Jantina, tblPeribadi.BumiStatus, 
  4.     CAST(DAY(tblPeribadi.Tarikh_Lahir) AS VARCHAR(2)) + ' ' + DATENAME(MM, tblPeribadi.Tarikh_Lahir) + ' ' + CAST(YEAR(tblPeribadi.Tarikh_Lahir) AS VARCHAR(4)) AS [Tarikh_Lahir],
  5.                       tblPeribadi.Tempat_Lahir,TblCountry.CDesc as Negara_Kelahiran,  tblstate.Statedesc as Negeri_Kelahiran, 
  6.                       tblMaritulStatus.MarStat as Taraf_Perkahwinan, 
  7.     CAST(DAY(tblPeribadi.Tarikh_Perkahwinan) AS VARCHAR(2)) + ' ' + DATENAME(MM,tblPeribadi.Tarikh_Perkahwinan) + ' ' + CAST(YEAR(tblPeribadi.Tarikh_Perkahwinan) AS VARCHAR(4)) AS [Tarikh_Perkahwinan],
  8.                       tblreligious.Rel_Denomination as Agama, tblEthnic.EthnicDesc as Bangsa, tblPeribadi.No_EPF, tblPeribadi.No_Socso, 
  9.                       tblPeribadi.No_Tax, tblPeribadi.Email  
  10. FROM         tblPeribadi INNER JOIN
  11.                       tblCompany ON tblPeribadi.Company_Code=tblCompany.Company_Code INNER JOIN
  12.                       tblGroup ON tblPeribadi.Kumpulan = tblGroup.Kumpulan INNER JOIN
  13.                       tblBusiness_Area ON tblPeribadi.Business_Area = tblBusiness_Area.Business_Area INNER JOIN
  14.                       tblCost_Center ON tblPeribadi.Kod_Pusat_kos = tblCost_Center.Kod_Pusat_Kos INNER JOIN
  15.                       TblCountry ON tblPeribadi.Negara_Kelahiran = TblCountry.CShort inner join 
  16. tblstate on tblPeribadi.Negeri_Kelahiran=tblstate.region inner join 
  17. tblMaritulStatus on tblPeribadi.Taraf_Perkahwinan=tblMaritulStatus.MarCode inner join
  18. tblreligious on tblPeribadi.Agama=tblreligious.relcode inner join 
  19. tblethnic on tblPeribadi.Bangsa=tblEthnic.EthnicCode
  20. 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
Oct 17 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
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.Kumpulan = NULL use this

Expand|Select|Wrap|Line Numbers
  1. 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
Oct 17 '08 #2

P: 83
Thanks I try my best
Oct 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.