SQL Exists, or IS IN functionality.

[Stewart_Whitwor]

Wanted to get a list of customers who fit a certain criteria based upon an orderlines table.

The table has a column for every order where the order type is either A or B eg.

OrderId	CustomerId	Type
1	300	A
2	300	B
3	301	A

I would like to get a list of customers who have only ever used exclusively Type A - 

So in my example I would like to return just CustomerId 301, as they have never made a B order.

Usually I would write something like;

SELECT CustomerId FROM orders WHERE CustomerId NOT IN (
        SELECT DISTINCT CustomerId FROM orders WHERE Type = B

)

Is there any nifty way to perform this type of logic from within an explore? Or any way other than using a derived table?

(Due to a very crowded explore, I try to discourage the use of new measures that focus on an edge case scenario if it can be avoided)

[JenniferThomas · Accepted solution]

You could have a couple of measures:

measure: order_typeA_count {

   type: count_distinct

   filters: [type: “A”]

   sql: ${orderID};; }

measure: order_typeB_count {

   type: count_distinct

   filters: [type: “B”]

   sql: ${orderID};; }

add CustomerId as column in your report. Add order_typeA_count > 0 filter and order_typeB_count = 0 filter.