Cindy,

Thanks for your reply. I moved WHERE restriction in my test program the way you suggested and this query produced zero records. I want an opposite - I want one record for this town with NULL values in each field. This is the way to determine the difference by formatting and report engine. I mean, I want to print for this town: "No transactions", so I need to have a record for this, some sort of flag. May be it's possible in SQL and I don't need to join Towns table at all?


>Move the WHERE restrictions on the secondary tables to the join clauses like:
>
>

<b>
>INNER JOIN SiteMstr ON SiteMstr.Town = Towns.Town AND SiteMstr.Town = "GOSN"
></b>

>
>Leave only conditions on TranMstr in the WHERE clause. The WHERE takes precedence over the join and will not return nulls to match the TranMstr records.
>
>(I just ran into this same problem last week!!)
>
>
>
>>I want to have this record in returning result with NULL in all fields. How can I achieve this?
>>
>>This is my SQL:
>>

>>SELECT * FROM tranmstr ;
>left JOIN misclndr on tranmstr.tranid = misclndr.tranid ;
>inner JOIN propmstr on tranmstr.propid = propmstr.propid ;
>inner JOIN sitemstr on tranmstr.propid = sitemstr.propid ;
>left JOIN bldgmstr on tranmstr.propid = bldgmstr.propid ;
><b>right join Towns on SiteMstr.Town=Towns.Town</b> ;
>where (prefcode = "P" and sitemstr.town="GOSN" and SOURCE="S") ;
>and between(tranmstr.extrYear+tranmstr.extrWeek ,oJC.LowWeek, oJC.HighWeek) ; INTO TABLE &qry_arg3
>>