>Hi,
>
>I have been struggling with the following issue.
>
>I will start with the SQL Select that works:
>
>select * from work_his join eq_file on eq_file.id_number = work_his.id_number and eq_file.site_no = 8
>
>
>The above selects records from the table WORK_HIS. No problem.
>
>Now I want to add a WHERE so that only more recent records are selected. Here is the SQL Select:
>
>select * from work_his join eq_file on eq_file.id_number = work_his.id_number and eq_file.site_no = 8
>where cast(DATE_CLOS as date) = ( Select MAX( cast(DATE_CLOS as date)) from work_his)
>
>
>That is, I added the WHERE to the above SQL Select. Now No records are selected.
>
>What am I missing?
>
>TIA
I figured the cause of the problem.
The WHERE clause simply finds more recent (largest) records. But those records do not match the JOIN clause.
Hence, no records are selected.
By adding work_his.site_no = 8 to the WHERE clause, I find records.
UPDATE: Another approach:
I moved the JOIN from to the sub-query. This works.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
