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 :) :)
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.