Selecting all data from view where forign key = null

Posted by Ahmedsa under C# on 9/9/2014 | Points: 10 | Views : 420 | Status : [Member] | Replies : 1
Hi guys i have view like following :
SELECT dbo.Nationality.NationalityName, dbo.Employee.DriverName, dbo.Employee.DriverID, dbo.Employee.NationalityID, dbo.Employee.ResidentNo,
dbo.Country.CountryName, dbo.Employee.CountryID, dbo.Branch.BranchName, dbo.Employee.BranchID, dbo.Employee.JoinDate,
dbo.Employee.ResignDate, dbo.Employee.HealthCarNo, dbo.Employee.JobID, dbo.Jobs.JobName, dbo.Department.DepartmentName,
dbo.Jobs.DepartmentID, dbo.Employee.PlaceIssue, dbo.Employee.Deduction, dbo.Employee.ExpireDateMedical, dbo.Employee.PolicyNumber,
dbo.Employee.Owner, dbo.Employee.Version, dbo.Employee.ExpireDateResident, dbo.Employee.Salary, dbo.Employee.SexID, dbo.Sex.SexType,
dbo.Sex.FlagSex, dbo.Employee.MiritialID, dbo.Miritial.MiritualStatus, dbo.Status.StatusType, dbo.Employee.StatusID, dbo.Employee.UnactiveReason,
dbo.Employee.BirthDate, dbo.Employee.DateToday, dbo.Employee.UserID, dbo.Employee.PassportNo, dbo.Employee.Period,
dbo.Employee.AccountNo, dbo.Employee.Bonus, dbo.Employee.AccountType, dbo.Employee.PlaceOfBirth, dbo.Employee.EmplyeeName,
dbo.Employee.ExpireDateresidentHijri, dbo.Employee.PassportDateStart, dbo.Employee.PassportDateExpire, dbo.Religon.ReligonName,
dbo.Employee.ReligonID
FROM dbo.Nationality INNER JOIN
dbo.Employee ON dbo.Nationality.NationalityID = dbo.Employee.NationalityID INNER JOIN
dbo.Country ON dbo.Employee.CountryID = dbo.Country.CountryID INNER JOIN
dbo.Branch ON dbo.Employee.BranchID = dbo.Branch.BranchID INNER JOIN
dbo.Jobs ON dbo.Employee.JobID = dbo.Jobs.JobID INNER JOIN
dbo.Department ON dbo.Jobs.DepartmentID = dbo.Department.DepartmentID INNER JOIN
dbo.Sex ON dbo.Employee.SexID = dbo.Sex.SexID INNER JOIN
dbo.Miritial ON dbo.Employee.MiritialID = dbo.Miritial.MiritialID INNER JOIN
dbo.Status ON dbo.Employee.StatusID = dbo.Status.StatusID INNER JOIN
dbo.Religon ON dbo.Employee.ReligonID = dbo.Religon.ReligonID
suppose i need to show all data from Employee table (NationalityName,BranchName,JobName) where
NationalityID =NULL OR BranchID=NULL OR JobID=NULL
WHAT I DO
Notes : I mean in topic(selecting all data from view not table
meaning i need to show all data in employee table by view
in case of forign key(any forign key)equal null




Responses

Posted by: Bandi on: 9/9/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Use LEFT JOIN where ever the master table is joined to Employee table

like below,

SELECT dbo.Nationality.NationalityName
,dbo.Employee.DriverName
,dbo.Employee.DriverID
,dbo.Employee.NationalityID
,dbo.Employee.ResidentNo
,dbo.Country.CountryName
,dbo.Employee.CountryID,
dbo.Branch.BranchName,
dbo.Employee.BranchID,
dbo.Employee.JoinDate,
dbo.Employee.ResignDate,
dbo.Employee.HealthCarNo,
dbo.Employee.JobID,
dbo.Jobs.JobName,
dbo.Department.DepartmentName,
dbo.Jobs.DepartmentID,
dbo.Employee.PlaceIssue,
dbo.Employee.Deduction,
dbo.Employee.ExpireDateMedical,
dbo.Employee.PolicyNumber,
dbo.Employee.Owner,
dbo.Employee.Version,
dbo.Employee.ExpireDateResident,
dbo.Employee.Salary,
dbo.Employee.SexID, dbo.Sex.SexType,
dbo.Sex.FlagSex, dbo.Employee.MiritialID, dbo.Miritial.MiritualStatus, dbo.Status.StatusType, dbo.Employee.StatusID, dbo.Employee.UnactiveReason,
dbo.Employee.BirthDate, dbo.Employee.DateToday, dbo.Employee.UserID, dbo.Employee.PassportNo, dbo.Employee.Period,
dbo.Employee.AccountNo, dbo.Employee.Bonus, dbo.Employee.AccountType, dbo.Employee.PlaceOfBirth, dbo.Employee.EmplyeeName,
dbo.Employee.ExpireDateresidentHijri, dbo.Employee.PassportDateStart, dbo.Employee.PassportDateExpire, dbo.Religon.ReligonName,
dbo.Employee.ReligonID
FROM dbo.Nationality
LEFT JOIN dbo.Employee ON dbo.Nationality.NationalityID = dbo.Employee.NationalityID
INNER JOIN dbo.Country ON dbo.Employee.CountryID = dbo.Country.CountryID
LEFT JOIN dbo.Branch ON dbo.Employee.BranchID = dbo.Branch.BranchID
LEFT JOIN dbo.Jobs ON dbo.Employee.JobID = dbo.Jobs.JobID
INNER JOIN dbo.Department ON dbo.Jobs.DepartmentID = dbo.Department.DepartmentID INNER JOIN dbo.Sex ON dbo.Employee.SexID = dbo.Sex.SexID
INNER JOIN dbo.Miritial ON dbo.Employee.MiritialID = dbo.Miritial.MiritialID
INNER JOIN dbo.Status ON dbo.Employee.StatusID = dbo.Status.StatusID
INNER JOIN dbo.Religon ON dbo.Employee.ReligonID = dbo.Religon.ReligonID


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response