Oleg,

I tried your suggestion and this query also produced 0 records. I have a filling, that I can not achieve my goal in just one query. We discussed this problem with my colleague and basically agreed, that here should be additional step. It requieres, unfortunately, couple of modifications in our current applications, but we will do them, if necessary.

>if 2 SELECTs give you result you want (not sure I understood what you expect,
> is it 1 rec per town or just 1 rec at all?)

The situation is: in TranMstr I have information about transactions. (price, date, mortgage, so on). in SiteMstr I have an address information. This is quite usual situation, that for particular time period the particular town has no transactions at all. So, in my final output result (report) I want to show this town with "No transactions". Futhermore this town requieres one 'NULL' record.
The SQL, which I showed, of course, just an example of this situation. List of towns (or counties, or whole state) is generated by one application (it returns whole where expression). The other application runs the resulted SQL.



>just combine them :
>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 ;
> and sitemstr.town="GOSN" ;
>left JOIN bldgmstr on tranmstr.propid = bldgmstr.propid ;
>right join Towns on SiteMstr.Town=Towns.Town ;
>where (prefcode = "P" and Towns.town="GOSN" and SOURCE="S") ;
>and between(tranmstr.extrYear+tranmstr.extrWeek ,oJC.LowWeek,oJC.HighWeek) ;
>INTO TABLE &qry_arg3
>
>BTW, what about "prefcode" and "SOURCE"?

Prefcode is a field in SiteMstr and source from TranMstr.

>Where are they coming from?
>If they belong to inner table of outer joins (like "misclndr" or "bldgmstr"), make sure you want them in WHERE clause, not in JOIN ON.
>HTH
>Oleg

Thanks.