Can you join two views without a sql_on statement?

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} ;;

}

}

This content is subject to limited support.                

Comments
tumikosha
Participant II
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?

sam8
Staff

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!

Version history
Last update:
‎04-05-2021 03:15 PM
Updated by: