Hi Nick,
>
>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


This is my test:

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 ;
where (prefcode = "P"  and SOURCE="S") ;
and between(extrYear+extrWeek ,'200004', '200004') ;
into cursor tttt


** The first SQL produced 0 records (there is no transactions for this town)

select * from tttt right join towns on tttt.town_a=towns.town where towns.town='GOSN'

** The second SQL produced one record (as I desire) with NULL in all tttt fields and Towns fields for this town.

So, now is the question: how can I combine this 2 SQLs into one and get the same result? Is it possible without joing with Towns table? (I don't need an extra information from Towns)

Thanks in advance.






>>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?