>>>Hi everybody,
>>>
>>>If I have SQL like
>>>
>>>select table1.* from table1 inner join table2 on table1.field1=table2.field1 and table1.field2=table2.field2
>>>
>>>and tables have empty field2, these records are not included into final result.
>>>
>>>How can I include them into result?
>>>
>>>Thanks a lot in advance.
>>
>>Field2 fields possibly have NULL value.
>
>No, it didn't have NULL value. Here is the problem and Jason explained to me in e-mail, what it currently doesn't have a solution (or we have to find it).
>
>I have a table1 with historical data. MAPREF is a Key Field. The file has several records for one MapRef with different LstSlDate. LstSlDate could be empty and also I have several records with the same date for the same MapRef.
>
>We need to produce a result with only one record per MapREF with the Max date. If date is empty, we need only one record. For same records I guess, we can take either one of them.
>
>So, we have to
>1. Group by MapRef, LstSlDate to exclude duplicates from original file
>2. Select Max date, MapRef from this file
>3. Join it with unduplicate file to produce a result.
>
>I will try it in a moment.
You may try also:
2. Select LstSlDate, MapRef from this file sorted by LstSlDate Desc (so recent go first). Then take the first one using Top nnn clause.
Igor Gelin
Database Developer