Merge 2 columns from different SQL server table with empty lines

Question

I have this query to achieve my goal:

SELECT 
    Datum, LeavetypeID 
FROM 
    Kalender 
INNER JOIN 
    VerlofLijn ON Kalender.ID = VerlofLijn.DagID 
WHERE 
    VerlofLijn.Persnummer = @pnummer;

So basically I have a calendar in the table Kalender.

Every day has a unique id(Kalender.ID). In a second table (VerlofLijn) the matching Kalender.ID is stored in VerlofLijn.DagID, together with the leavetype and the unique employee number Persnummer.

What I want to achieve is a query that loads all dates from the calendar, and - if the current logged-on employee has leave in the database - show this next to the correct date.

So if there is no leave at all in the database for this employee, I still need the calendar to show up so in a next step he can add leave to his personal calendar.

I could create a personal calendar for every employee, but there has to be a better way, without the overhead of storing way to much data in the database, which will make the query to take much more time to search and complete.

Answer 1

Please try this query

SELECT 
    Datum, LeavetypeID 
FROM 
    Kalender 
LEFT JOIN 
    VerlofLijn 
ON Kalender.ID = VerlofLijn.DagID AND VerlofLijn.Persnummer = @pnummer;

Explanation: As you mentioned

i want to achieve is a query that loads all dates from the calendar

you should use Kalender LEFT JOIN VerlofLijn

Answer 2

Have you tried LEFT OUTER JOIN like this?

SELECT 
    Datum, LeavetypeID 
FROM 
    Kalender 
LEFT OUTER JOIN
   VerlofLijn ON Kalender.ID = VerlofLijn.DagID 
WHERE 
   VerlofLijn.Persnummer = @pnummer;

You get all the values from Kalendar even if there are no matches from the table VerlofLijn, and all the common values from VerlofLijn.

You can find some examples here: What is the difference between "INNER JOIN" and "OUTER JOIN"?