Hi!

That is why I said to LEFT JOIN the RESOURCE table. INNER JOINED with ALLOCATION should return no record, right? But for main result you still want a record from the with all nulls to get a main records, right?

When you left join ALLOCATION, then left join RESOURCE, it will work correctly, but you will get the record from ALLOCATION and no record from RESOURCE (all nulls). Does this matter? Usually not, however, if yes (you already built interface that cannot work when allocation record is correct and no resource), you can use expression for getting fields from ALLOCATION, somethign like following:

SELECT ...., iif(RESOURCE.ID IS NULL,NULL,ALLOCATION.NAME) as Allocation_Name, ...

This way you will reach it.


>>Hi!
>>
>>What you meant? Sorry, I still bad in English and do not know meaning of many standard phrases.
>
>I meant that your response was a quick and simple one without answering my question.
>
>>
>>Really, the result might be not what you want. For example, when you have no record in Allocation and record in Resource, you will not get that record. This is because Resource linked to the Allocation only, so it is depended on it. When you want to retrieve Resource when no allocation, you require to left join Resource table to Client or Referral.
>
>Well, basically I want the ALLOCATION and RESOURCE records INNER JOINED (RESOURCE is only related to ALLOCATION) so that even if there isn't an ALLOCATION, it will return a row. But if there is no RESOURCE, but there IS an ALLOCATION record, then I still want it to display. But if I INNER JOIN the 2, then it stops the whole row from returning - this I don't think is right (or it's just a pain and I'll have to PUWI!) because I'm not INNER JOINING the RESOURCE to REFERRAL, I'm INNER JOINING RESOURCE to ALLOCATION, which is LEFT OUTER JOINING to REFERRAL - do you know what I mean?
>
>Kev
>
>
>
>>
>>>Vlad
>>>
>>>That's a response that favours you greatly :)
>>>
>>>I take it you'd rather not go into it?
>>>
>>>Kev
>>>>Hi!
>>>>
>>>>Just LEFT JOIN the Resource table ;)))
>>>>
>>>>>Hi
>>>>>
>>>>>I have the following Select:
>>>>>
>>>>>

>>>>>SELECT Referral.*;
>>>>>  FROM Referral;
>>>>> INNER JOIN Client;
>>>>>    ON Referral.Cl_Ref = Client.Cl_Ref;
>>>>>  LEFT OUTER JOIN Allocation;
>>>>>    ON Client.Cl_Ref = Allocation.Cl_Ref;
>>>>> INNER JOIN Resource;
>>>>>    ON Allocation.Rs_Ref = Resource.Rs_Ref;
>>>>> WHERE BETWEEN(Referral.Re_Date, ldStartDate, ldEndDate);
>>>>>  INTO CURSOR Results
>>>>>

>>>>>
>>>>>Now, my question is : Will the 2nd Inner Join restrict the results if there is no RESOURCE record relating to the ALLOCATION record. What I want to is for it to still retrieve the REFERRAL records, even if there is no related record between RESOURCE & ALLOCATION, because I'm only LEFT Joining the ALLOCATION.
>>>>>
>>>>>The solution is simple, change the INNER to a LEFT OUTER, so basically, I'm just confirming what my thoughts are.
>>>>>
>>>>>Any ideas?
>>>>>Thanks
>>>>>Kev