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.
