468,316 Members | 2,059 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

INNER JOIN & IS NOT NULL probs

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
2 4170
ck9663
2,878 Expert 2GB
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
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.

Similar topics

3 posts views Thread by Ike | last post: by
4 posts views Thread by Steve Hall | last post: by
3 posts views Thread by Prem | last post: by
18 posts views Thread by Dave | last post: by
3 posts views Thread by Zeff | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.