Knowledge Drop

Can you join two views without a sql_on statement?

  • 6 April 2021
  • 2 replies
  • 64 views

Userlevel 4

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.                

 


2 replies

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?

Userlevel 5
Badge

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!

 

Reply