... LEFT JOIN RF1 ; On NET.Operator == RF1.Operator ; and ( NET.Route == RF1.ERoute ; or EMPTY( RF1.ERoute) ) ; ...>>Sorry Sergey, I don't understand. I'm not intending to do "strange experiments". Your reply is very vague. I told you originally had that clause in, and got wrong results, and this was before I was advised to LEFT JOIN.
>SELECT DISTINCT NET.Operator, NET.Route, NET.Start_Date, NET.End_Date, NET.RorF, NET.Passengers, ; > IIF( NET.Route == NET.Mcl_Rte, " ", NET.Mcl_Rte) as ContrNum, NET.Family, ; > 0000000 AS CurrPass, 0000000 AS PrevPass, op.Desc as OpDesc, ; > NVL(RF1.ServType, "?") as ServeType ; > FROM NotETM NET ; > JOIN Operator op ; > On NET.Operator == op.Code ; > LEFT JOIN RF1 ; > On NET.Operator == RF1.Operator ; > and NET.Route == RF1.ERoute ; > WHERE ( ( m.ldStartCurr BETWEEN NET.Start_date AND NET.End_date) ; ... >