Query Layer comes up empty

[Jen_Zumbado-Hannibal]

Dear community, 

I'm having difficulty figuring out why this SQL query is coming up empty when creating a 'Query Layer.' 

I have two layers in the electrical network on SDE. Database on MSSQL 19 and using ArcPro 2.9.2. 

Relationship: one eTransformerBank [Field Name: FacilityId, Alias:Transformer ID, (Data Type: Long)] has many eTransformerUnit [Field Name: FacilityId, Alias: Facility Identifier, (Data Type: Text)].

Query:

select T.OBJECTID,FC.Shape,FC.FacilityId,FC.TransformerType,FC.UnitCount,FC.Project,FC.MapId,T.Phase,T.Voltage, CAST(FLOOR(T.kvaRating) AS varchar) AS Label from ForestGrove.GIS.eTransformerBank_evw AS FC LEFT OUTER JOIN ForestGrove.GIS.eTransformerUnit_evw AS T ON FC.FacilityId=T.TransformerBankId

The query is validated, and I choose FacilityID as a unique identifier to set the spatial properties of the table. 

However, once I do this, I get a table with no data. 

What am I doing wrong? 

[Jen_Zumbado-Hannibal · Accepted solution]

What are the chances? Someone just posted a question that got me thinking about the JOIN. I read somewhere that OUTER JOIN is the same as JOIN. So, I took out the OUTER and it worked! Yay! 

select T.OBJECTID,FC.Shape,FC.FacilityId,FC.TransformerType,FC.UnitCount,FC.Project,FC.MapId,T.Phase,T.Voltage, CAST(FLOOR(T.kvaRating) AS varchar) AS Label from ForestGrove.GIS.eTransformerBank_evw AS FC LEFT JOIN ForestGrove.GIS.eTransformerUnit_evw AS T ON FC.FacilityId=T.TransformerBankId

[Jen_Zumbado-Hannibal · Accepted solution]

UPDATE: 

I also wanted to show up the Bank FacilityID and the Unit FacilityID in the same table. So, I changed the query to:

select T.OBJECTID,FC.Shape,FC.FacilityId,T.FacilityId AS UnitFacID,FC.TransformerType,FC.UnitCount,FC.Project,FC.MapId,T.Phase,T.Voltage, T.kvaRating from ForestGrove.GIS.eTransformerBank_evw AS FC LEFT JOIN ForestGrove.GIS.eTransformerUnit_evw AS T ON FC.FacilityId=T.TransformerBankId

I'm not very good with SQL. But I understood what the query was doing. Since both tables have FacilityID, the outcome of the query layer couldn't have attributes with the same name even when displaying different data. So, I had to give it a label different than FacilityID (Bank) to make it work.