Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Any known equivalent to SQL "NOT in"...?

  • 4 October 2017
  • 6 replies
  • 1121 views
davehoy27

Hi - relatively new with Looker but old SQL person. This may be a simple question.



I have users that are trying to build a Look that lists out rows from a Master view that lack any activity in a transaction view. To be specific, they have a master view of students (one record per student), and a transaction view of job applications. I have measures in the model to count applications, but because of the way the JOIN works, it is only showing those students with at least one application transaction.



In SQL I would simply “select … from students where studentID NOT in (select studentID from applications)”



How can a user accomplish this in a Look? Thanks!

6 replies

emma.ware
Rank
Userlevel 2

Hi @davehoy27,



This sounds like a good time to use a derived table, which allows you to create a table of data that does not already exist in your database, and inject SQL into a LookML table.



In your LookML model, you would want to add something like the following:


derived_table: { sql: SELECT * FROM database_name.students WHERE studentID NOT IN (SELECT studentID FROM database_name.applications) ;; }



This would create a table of only students whose IDs are not in the application table. We have more on derived tables and their types in this Discourse Article.



Please let me know if you have any further questions!

lloydtabb
Rank
Userlevel 6
Badge

The easiest way to do this is to create a lifetime fact table, then join it in on user_id. You can then filter from the explore where ‘lifetime_transactions = 0’



explore: students {


   joins: student_lifetime_applications {


      sql_on: ${student_lifetime_applications} = ${students.student_id}


      relationship: one_to_one


  }


}





view: student_lifetime_applications {


   derived_table: {


      sql:  SELECT student_id, COUNT(*) as lifetime_transactions FROM applications


   }


   dimension: student_id{}


   dimension: lifetime_transactions{ type: number }


}
davehoy27

Thank you Emma. I’ve built a few derived tables and will consider it. I was hoping to get all of the inherent flexibility of Looker in the “NOT” looks; for example, show me all the students that have not had activity in a user-specified date range, etc…

davehoy27

Interesting approach, thanks Lloyd.

lloydtabb
Rank
Userlevel 6
Badge

re-reading this, you may have to handle null, students that have no transactions would have null instead of 0 transactions so COALESING (converting nulls to 0) is probably in order.



view: student_lifetime_applications {


   derived_table: {


      sql:  SELECT student_id, COUNT(*) as lifetime_transactions FROM applications


   }


   dimension: student_id{}


   dimension: lifetime_transactions{ 


     type: number 


     sql: COALESCE(${TABLE}.lifetime_transactions, 0) ;;


   }


}
Ana1

@lloydtabb, how is this filtering out in the explore where ‘lifetime_transactions = 0’?


Also, how would you achieve a not exist in looker?

Reply


Terms of Service