Joining to a table multiple times in FM

[craigalaniz]

Hello

I am attempting to join a table multiple times in FM, but I believe i am doing some thing wrong.

Example

I have a Patient table that has 2 different ProviderIds in it. I need to LEFT join to the Provider table to get the Provider data for both. When I create 2 relationships in FM I am only getting one back. I can do this in SQL but I am not sure what I am doing wrong in FM.


In FM I am creating 2 left joins(0..1) from the Patient table to the Provider table.




SQL

SELECT
       attend.*,
       er.*

FROM dbo.DW_Patient_Encounters pe

   LEFT JOIN DW_Provider attend
ON pe.AdmitProviderID = attend.ProviderID
   AND pe.SourceID = attend.SourceID
   
      LEFT JOIN DW_Provider er
ON pe.ErProviderID = er.ProviderID
   AND pe.SourceID = er.SourceID
     
     
WHERE pe.AccountNumber = 'XXXXX'



Thanks in advance for your help  :) :)

[bus_pass_man]

If you put all 4 pairs into one relationship do you get the result you are seeking?

Or is it a role-playing scenario, in which case the exercise is of a somewhat different nature.

The query engine needs unambiguous relationships defined so it can plan the SQL in a consistent and knowable fashion.  If there is ambiguity, the query engine will take the alphabetically first one and use it.