Can you join two views without a sql_on statement?

Looker Tips & Resources Technical Tips & Tricks
1

Knowledge Drop

Last tested: May 15, 2020

Joins in explores have to have either a sql (docs) or sql_on (docs) parameter to define the relationship of one view to another.

Using sql_on:

explore: order {

join: customer {

sql_on: ${order.customer_id} = ${customer.id} ;;

}

}

Using sql:

explore: order {

join: customer {

sql: LEFT JOIN ON ${order.customer_id} = ${customer.id} ;;

}

}

2 
join: customer {

     sql: LEFT JOIN ON ${order.customer_id} = ${customer.id} ;;

}

I tried this advice, but got an error:

Query execution failed: - Syntax error: Unexpected keyword ON at [….]

Looks like we need a table name between JOIN and ON
How to specify it?

3

Hi @tumikosha ! You can specify the table name by hard-coding it like so:

join: customer {
  sql: LEFT JOIN public.customer on ${order.id} = ${customer.id} ;;
}

Unfortunately, there is no way to reference the table name as defined in the LookML view while writing in the model file. This one of the reasons that sql is a deprecated parameter, and sql_on is recommended instead!

1 Like