Hi Nadya,

I would suggest to break this SQL down to several SQLs and see where exactly you stop getting the data. Then you may try to combine it back again (or not).

Nick

>Ken,
>
>>Two poosibilites off the top here :
>>
>>1. If removing the right join yields no records maybe the where clause condition is never satisfied or one of the inner join condition is not met. Do all the tables have the proper "propid" values?
>
> Where condition should produce 0 records for this town, because there are no transactions for this town in this time period. That's why I added an additional join, trying to get an 'empty' record for this town. It seems not working this way.
>
>>
>>2. Is the "right join" correct? Maybe it should be a "left join"? It seems
>
> I tried left join also. No luck.
>
>>
>>>Hi everybody,
>>>
>>>I'm trying to select something without luck. Let me describe the problem:
>>>
>>>I have several relational tables, one of which contains information about transactions. My form allows me to specify different criteria, like geography, for example. Say, I select a town, for which I have no transactions in a particular time period. 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
>>>

>>>
>>>The selected part doesn't work. VFP holds up. If I exclude right join Towns, I receive zero (0) records. How can I change this SQL? Is it possible in just one SQL?