SQL JOIN and WHERE statement

Question

I have a problem getting this sql statemen to return what I want: I want it to return a list of properties both the employee or Job_Profile. If one of them do not have the property it should return NULL in that row/column Now the sql looks like:

SELECT Parameter.Patameter_Description ParamName, 
       Job_Profile.Title, Job_Property.Mark JobMark, 
       Emp_Property.Mark EmpMark, 
       Emp_Id--, (Employee.First_Name + ' ' + Employee.Last_Name) EmpName
FROM Job_Property
INNER JOIN  Job_Profile ON Job_Profile.Title = Job_Property.Job_Title
INNER JOIN Parameter ON Job_Property.Parameter_Id = Parameter.Id
RIGHT JOIN Emp_Property ON Emp_Property.Parameter_Id = Job_Property.Parameter_Id
INNER JOIN Employee ON Emp_Property.Emp_Id = Employee.Enterprise_Staff_Id

WHERE Employee.Enterprise_Staff_Id = 22 
AND Job_Profile.Title = 
    (SELECT 
       Employee.Job_Profile_Name 
       FROM Employee WHERE Employee.Enterprise_Staff_Id = 22)

The result is:

Analyse test 1 3 22

And I would like it to be something like this:

Analyse     test     1      3       22

Data        test     3      NULL    NULL or 22

economic    test     4      NULL    NULL or 22

Service     test     2      NULL    NULL or 22

I know there is a problem when I: - join Emp_Property - Make the WHERE statement

Answer 1

Try LEFT OUTER JOIN when joining Emp_Property

Answer 2

Agree with Danny, use the 'LEFT OUTER JOIN' method instead of 'INNER JOIN' as this will only return rows where an entry is found in both tables.

Answer 3

I found a solution, I had to make temp tables and join them:

CREATE TABLE #CompareJob
(Parameter_Id INT
,Parameter_Name VARCHAR(MAX)
,Jobprofile VARCHAR(30)
,Job_Mark INT
)
INSERT INTO #CompareJob(Parameter_Id,Parameter_Name, Jobprofile ,Job_Mark)
    SELECT  Parameter.Id, Parameter.Patameter_Description, Job_Profile.Title, Job_Property.Mark
    FROM Job_Property
    INNER JOIN  Job_Profile ON Job_Profile.Title = Job_Property.Job_Title
    INNER JOIN Parameter ON Job_Property.Parameter_Id = Parameter.Id
    WHERE Job_Profile.Title = (SELECT Employee.Job_Profile_Name FROM Employee WHERE Employee.Enterprise_Staff_Id = 22)

CREATE TABLE #CompareEmp
(Parameter_Id INT
,Parameter_Name VARCHAR(MAX)
,Emp_Id INT
,Emp_Name VARCHAR(100)
,Emp_Mark INT
)
INSERT INTO #CompareEmp(Parameter_Id,Parameter_Name, Emp_Id , Emp_Name ,Emp_Mark)
    SELECT  Parameter.Id, Parameter.Patameter_Description, Employee.Enterprise_Staff_Id, (Employee.First_Name + ' ' + Employee.Last_Name) empname, Emp_Property.Mark
FROM Emp_Property
INNER JOIN Employee ON Employee.Enterprise_Staff_Id = Emp_Property.Emp_Id
INNER JOIN Parameter ON Parameter.Id = Emp_Property.Parameter_Id
WHERE Employee.Enterprise_Staff_Id = 22

SELECT * FROM #CompareJob
FULL OUTER JOIN #CompareEmp ON #CompareJob.Parameter_Id = #CompareEmp.Parameter_Id