Left join not operating correctly, appears to be working as an inner join

[somaxr]

I have some finance data, some of which is actuals from the past until now (as a running total of small transactions) and the other data is budget data which runs from now until the future.

These data sets have 2 items in common, the month in which they relate to needs to be the same, and so does the nominal accounting code. They are both fact tables so do not have a natural primary key, but we have created concatenated primary keys for each. We are trying to join them via the use of a calendar table (which has dates from the past running well into the future, date is the primary key on this table) and a reference table for the nominal codes (the nominal code is primary key on this table).

we have created the following explore to join these together:

Calendar is used as the base table, and a left outer join is used to join the calendar dates to the other tables. However, when we visualise the data, it is limited by the date fields on the budget view, and will not let us see past data on the transactions view. As the calendar table runs into the past, and it is a left outer join, why is this data being limited to the date fields in the budget table as if it were an inner join?

(note: we have tried various combinations of the many:many, many:one etc just in case we had got this wrong, and have commented out the ‘max_date’ view to ensure this wasn’t causing the issue).

[Maddie]

Hi somaxr,

Would it be possible to show a snip of the explore and the SQL generated?

It would help us understand what might be going wrong.

On a separate note, if the calendar table is at day level, the join relationship is going to be many to many (as there are multiple days in each month).

Best,
Maddie | Redkite

[cole_elliott]

Hi @somaxr, as @Maddie alluded to, seeing the SQL that’s generated in the actual use case you’re describing would be helpful to rule out at least one possible culprit:

if any fields from the left joined views are used in Filters, then that can result in the left joins functioning effectively as inner joins, because the Filters result in WHERE clause statements being added to the query that Looker generates, which will filter out any records from the left joined tables having a NULL value for those particular filters.

Typically, when actually writing SQL, if you want filters to be applied in a left join but don’t want the left joined table’s records where the value of the field in question is NULL to actually be limited by that condition, then you would instead throw those in the LEFT JOIN ... ON clause, allowing the records that have NULL values for the particular field used in the condition to still be returned in the result set.

I’m definitely willing to admit that I am ignorant on some Looker magic for this particular case - that is needing to apply a filter to a left joined table such that records where the field in question is NULL aren’t excluded - the generated query throws the condition in the LEFT JOIN ... ON... rather than in the WHERE clause - but as far as I know, if you need this capability, you may have to use derived tables and templated filters/parameters, or Merge Queries.

Like I said earlier, feedback on this is welcome - I’d love to find out there is a better solution to dealing with these use cases.

Apologies in advance @somaxr if this is not at all related to what you’re doing/what your use case is.

[Cyril_MTL_Analy]

Hey there,

From what I gather here, I think the issue comes from your hybrid sql_on statement (access_transactions join) that is on both the calendar and finance_budget views.

Essentially here, you are limiting the data of the access_transactions view to any record that has the same months as the finance_budget view along with the same nominal_code. With this logic you can only get data from budget_month.

Try removing that part: AND ${finance_budget.nominal_code} = ${access_transactions.nominal_code}