I view the purpose of the join as being a filter on only jobs which match in both tables. If so, then what you really want here is an inner join, and the check on the date being the current date or earlier should be moved to the where clause. Putting this together, perhaps this is what you need here:
SELECT MAX(j1.joiningDate)
FROM public.job1 j1
INNER JOIN public.job2 j2
ON j1.id = j2.id
WHERE
j1.joiningDate::date <= CURRENT_DATE;
Note: Consider making the joiningDate
column an actual date column in both tables.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…