September 9, 2014 at 12:47 am
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
September 9, 2014 at 1:44 am
SELECT NationalityName, BranchName, JobName
FROM Employee
WHERE NationalityID IS NULL OR BranchID IS NULL OR JobID IS NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 9, 2014 at 1:54 am
Here's your original query reformatted and using table aliases:
SELECT
n.NationalityName, e.DriverName, e.DriverID, e.NationalityID, e.ResidentNo, c.CountryName,
e.CountryID, b.BranchName, e.BranchID, e.JoinDate, e.ResignDate, e.HealthCarNo, e.JobID,
j.JobName, d.DepartmentName, j.DepartmentID, e.PlaceIssue, e.Deduction, e.ExpireDateMedical,
e.PolicyNumber, e.[Owner], e.[Version], e.ExpireDateResident, e.Salary, e.SexID, dbo.Sex.SexType,
dbo.Sex.FlagSex, e.MiritialID, m.MiritualStatus, s.StatusType, e.StatusID, e.UnactiveReason,
e.BirthDate, e.DateToday, e.UserID, e.PassportNo, e.Period, e.AccountNo, e.Bonus, e.AccountType,
e.PlaceOfBirth, e.EmplyeeName, e.ExpireDateresidentHijri, e.PassportDateStart, e.PassportDateExpire,
r.ReligonName, e.ReligonID
FROM dbo.Nationality n
INNER JOIN dbo.Employee e
ON n.NationalityID = e.NationalityID
INNER JOIN dbo.Country c
ON e.CountryID = c.CountryID
INNER JOIN dbo.Branch b
ON e.BranchID = b.BranchID
INNER JOIN dbo.Jobs j
ON e.JobID = j.JobID
INNER JOIN dbo.Department d
ON j.DepartmentID = d.DepartmentID
INNER JOIN dbo.Sex
ON e.SexID = dbo.Sex.SexID
INNER JOIN dbo.Miritial m
ON e.MiritialID = m.MiritialID
INNER JOIN dbo.[Status]
ON e.StatusID = s.StatusID
INNER JOIN dbo.Religon r
ON e.ReligonID = r.ReligonID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 9, 2014 at 1:59 am
--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
-- Use outer joins on those columns, like this:
SELECT
n.NationalityName, e.DriverName, e.DriverID, e.NationalityID, e.ResidentNo, c.CountryName,
e.CountryID, b.BranchName, e.BranchID, e.JoinDate, e.ResignDate, e.HealthCarNo, e.JobID,
j.JobName, d.DepartmentName, j.DepartmentID, e.PlaceIssue, e.Deduction, e.ExpireDateMedical,
e.PolicyNumber, e.[Owner], e.[Version], e.ExpireDateResident, e.Salary, e.SexID, dbo.Sex.SexType,
dbo.Sex.FlagSex, e.MiritialID, m.MiritualStatus, s.StatusType, e.StatusID, e.UnactiveReason,
e.BirthDate, e.DateToday, e.UserID, e.PassportNo, e.Period, e.AccountNo, e.Bonus, e.AccountType,
e.PlaceOfBirth, e.EmplyeeName, e.ExpireDateresidentHijri, e.PassportDateStart, e.PassportDateExpire,
r.ReligonName, e.ReligonID
FROM dbo.Employee e
left JOIN dbo.Nationality n
ON n.NationalityID = e.NationalityID
INNER JOIN dbo.Country c
ON e.CountryID = c.CountryID
left JOIN dbo.Branch b
ON e.BranchID = b.BranchID
left JOIN dbo.Jobs j
INNER JOIN dbo.Department d
ON j.DepartmentID = d.DepartmentID -- Note
ON e.JobID = j.JobID -- Note
INNER JOIN dbo.Sex
ON e.SexID = dbo.Sex.SexID
INNER JOIN dbo.Miritial m
ON e.MiritialID = m.MiritialID
INNER JOIN dbo.[Status]
ON e.StatusID = s.StatusID
INNER JOIN dbo.Religon r
ON e.ReligonID = r.ReligonID
-- Note: You can join these two tables as shown, or alternatively inner join them together in
-- a subquery which is then outer-joined to the main query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply