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
2 4421
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 - 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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: robert |
last post by:
running 8.1.7 server, 8.1.6 client.
i *thought* inner join should not return nulls, but not only that,
but i get way more rows than i'm expecting.
assume:
order table:
order_number
|
by: Ike |
last post by:
Oh I have a nasty query which runs incredibly slowly. I am running MySQL
4.0.20-standard. Thus, in trying to expedite the query, I am trying to set
indexes in my tables.
My query requires four...
|
by: Steve Hall |
last post by:
Folks,
My secnario involves two tables - ObservationRegister, and Person.
ObservationRegister contains most of the "useful" fields, including
the UserID of the person that raised the record, and...
|
by: Prem |
last post by:
Hi,
I am having many problems with inner join. my first problem is :
1) I want to know the precedance while evaluating query with multiple
joins.
eg.
select Employees.FirstName,...
|
by: DCM Fan |
last post by:
{CREATE TABLEs and INSERTs follow...}
Gents,
I have a main table that is in ONE-MANY with many other tables. For example, if
the main table is named A, there are these realtionships:
A-->B...
|
by: mheydman |
last post by:
I apologize if this has been asked before- I searched google but could
not find a concrete answer.
I recently inherited a database whose t-sql code is written in a format
that I find difficult...
|
by: dmonroe |
last post by:
hi group --
Im having a nested inner join problem with an Access SQl
statement/Query design. Im running the query from ASP and not usng the
access interface at all. Here's the tables:
...
|
by: Dave |
last post by:
Guys I am really stuck on this one. Any help or suggestions would be
appreciated.
We have a large table which seemed to just hit some kind of threshold.
They query is somewhat responsive when...
|
by: Zeff |
last post by:
Hi all,
I have a relational database, where all info is kept in separate tables
and just the id's from those tables are stored in one central table
(tblMaster)...
I want to perform a query, so...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |