Getting data from a table even if there's no data in table 2 that links to table 1-CodePudding

I'm creating a planner but the SQL statement I have now only shows employees that have something in table 2 (plannerEntries) and doesn't show the rest of the employees from table 1 (Employee_List).

I need all of the employees to be outputted into the table regardless of whether they have any jobs assigned for them for the week, so that they can have new jobs assigned easily.

This is my current SQL code

SELECT [EL].[Employee_Numer], 
       [PP].[workDate], 
       [PP].[jobNo],    
       [PP].[workDescription], 
       [PP].[timeOfDay], 
       [JF].[Description], 
       [EL].[Forename], 
       [EL].[Surname]
FROM   plannerEntries AS PP
  RIGHT JOIN [Employee_List] AS EL
    ON [PP].[employeeNumber] = [EL].[Employee_Numer]
  INNER JOIN [Job File] AS JF
    ON [PP].[jobNo] = [JF].[Job No]
WHERE [PP].[workDate] >= '$monday'
AND   [PP].[workDate] <= '$sunday'
ORDER BY [PP].[employeeNumber] ASC;

I expected all employees to be printed regardless of records in table 2, however only the employees with records in table 2 were printed. The below image is the actual output.

CodePudding user response：

Please check the difference between inner join, left join, right join.

Something like this should do what you need:

SELECT
    [EL].[Employee_Numer],
    [PP].[workDate],
    [PP].[jobNo],
    [PP].[workDescription],
    [PP].[timeOfDay],
    [JF].[Description],
    [EL].[Forename],
    [EL].[Surname]
FROM
    [Employee_List] AS EL
    left join plannerEntries AS PP on [PP].[employeeNumber] = [EL].[Employee_Numer]
        and [PP].[workDate] >= '$monday'
        and [PP].[workDate] <= '$sunday'
    left join [Job File] AS JF on [JF].[Job No] = [PP].[jobNo]
ORDER BY
    [PP].[employeeNumber] ASC;