SQL - How to join tables with dates to get overall view of ALL dates

Question

Using a bespoke ERP system with T & A built in. Table 1 has dates with holidays types i.e. bank holidays, user booked holidays - and a user id.

Table 2 has all the clockings for that user id - so, clock in time and date, clock out time and date, user id

Is there a way to show all of the dates in a week, for example by amalgamating the two tables.

E.g. table 1 has a bank holiday on monday, users have clocked tue, wed, thu - or a user hasn't clocked at all because the holiday table has them off all week?

Answer 1

A Left Join or Right Join would do the trick.

SELECT T1.data1, T2.data2 FROM table1 AS T1
LEFT JOIN table2 AS T2
ON foreignKey1 = primnaryKey2

This will join the two tables including the null values from the "left" table. Right joins do the same with the right one. All depends on which table references the other in a Foreign Key. Note that in this example T1 is left and T2 is right.