Why is Looker not writing the Join condition in the generated sql? (sql_on)

[LookerExperts]

Knowledge Drop

Last tested: Jan 21, 2019
 

In all likelihood the LookML model syntax is using sql: INSTEAD of sql_on: Confirm that in your join you are using sql_on and not sql.

For example:

explore: deals {

join: fact {

relationship: one_to_one

sql_on: ${fact.offer_id} = ${deals.offer_id} ;;

}

join: lead {

relationship: one_to_many

sql_on: ${lead.offer_id} = ${deals.offer_id} ;;

}

join: provider_lead {

relationship: one_to_one

sql: ${lead.redirect_id} = ${provider_lead.redirect_id} ;;

}

The 3rd Join statement is using sql: and that will cause Looker to generate the following sql:

FROM deals

LEFT JOIN view.lead AS lead ON lead.offer_id = deals.offer_id

lead.lead_id = provider_lead.lead_id

It should be [LEFT JOIN] lead.redirect_id = provider_lead.redirect_id.
If you wanted to customize your join logic with sql make sure to write that in the parameter, such as

join: provider_lead {

relationship: one_to_one

sql: RIGHT JOIN ${provider_lead.lead_id} ON ${lead.lead_id}= ${provider_lead.lead_id} ;;

}

Otherwise, just change the sql to sql_on so Looker can write the join for you!

This content is subject to limited support.