Bug when using left joins in custom tables
In data model custom tables, you can do left joins. For example, Select * From ProductsTable Left Join SalesTable. But there's a bug in the "On" clause causing them to sometimes become Inner Joins.
Simple "On" clauses work, such as "On ProductsTable.ProductID = SalesTable.ProductID".
Some complex "On" clauses cause the Left Join to become an Inner Join. For example, my custom table below has zero rows in it. Instead, it should return a row for every Cost Pool Group, even if that doesn't have any matching Cost Pools.
SELECT * FROM dimCostPoolGroup LEFT JOIN dimCostPool ON
dimCostPoolGroup.[Cost Pool Group Code] <> '' AND
(
(dimCostPoolGroup.[Cost Pool Group Code] = 'abc' AND dimCostPool.[Cost Pool Code] <> 'abc') OR
(dimCostPoolGroup.[Cost Pool Group Code] <> 'abc' AND dimCostPool.[Cost Pool Code] = 'abc')
)
NOTE: Do not confuse this with other discussion around left joins in data model relationships. I'm talking about custom tables. In the examples above, none of the tables have relationships.
HamzaJ
·1 year ago+1
I have been experiencing this a lot lately. Very annoying.