Reputation: 169
I have two table
job1
id(c-v) | joiningDate(character-varying)
--+--
1 | 2020-10-05
2 | 2023-10-05
3 | 2021-01-01
job2
id(c-v) | joiningDate(character-varying)
--+--
1 | 2020-10-05
2 | 2023-10-05
Here is sql query
SELECT max(j1."joiningDate")
FROM public.job1 j1 left join public.job2 j2 ON j1.id=j2.id and
j1."joiningDate" <= TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')
I'm trying to fetch the maximum date which is less than current date.. for the I'm getting the empty data.. what would be the issue?
Upvotes: 1
Views: 1333
Reputation: 522817
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.
Upvotes: 1
Reputation: 95101
The query you are showing should not result in NULL, but in 2023-10-05 (the maximum date in the table). This is what you have:
SELECT max(j1.joiningDate)
FROM public.job1 j1
LEFT OUTER JOIN public.job2 j2 ON <conditions>
You see that the table job2 has no impact on the result (and nothing to do with the task at hand as far as I can see).
You want a WHERE
clause to restrict the rows to dates before today:
WHERE j1.joiningDate <= TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')
The whole query:
SELECT max(j1.joiningDate)
FROM public.job1 j1
WHERE j1.joiningDate <= TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD');
If you are really getting null (or no result) with your original query, then this is not a problem with the query. You should get a result as mentioned (albeit not the desired result at that). It must be your tool or app. Or you are not showing the complete query. Or there is no data in table job1.
Upvotes: 1