Null Eaddress Values

Brandon,

This is what I use

SELECT DISTINCT customer_no
FROM t_customer (NOLOCK)
WHERE customer_no not in
 (
 SELECT DISTINCT customer_no
 FROM vs_eaddress  (NOLOCK)
 WHERE Primary_ind = 'Y'
 )

I have this as a separate segement in the extraction and the segment has the Suppr Flag checked.  I know there's a lot of double negatives but it seems to work.

Dale

This is perfect!  Thanks, Dale!

Brandon -

Just to clarify in case you want to know where you went awry -- the reason your first query didn't work was you had an INNER join clause.

An inner join selects the data set where the data overlaps based on your ON statement. Since you were looking for someone who wouldn't exist in the vs_eaddress table (someone with no email right) this immediately wipes out all those people. 

However, if you made it a left join this is basically saying, give me everyone in the first table (t_customer) and then give me the joining information in the second table/view. However, if there is no information in that second table, just give me null columns.

Thus,

Select Distinct a.customer_no
 From t_customer a (NOLOCK)
 LEFT JOIN vs_eaddress e (NOLOCK) ON a.customer_no = e.customer_no
 Where  1 = 1
 AND e.address is null

would have gotten you closer to the goal. Dale's statement is actually more useful in my opinion because you are looking for anyone who has that primary email address and the join above could include inactive addresses, but that's another conversation.

Just in case you wanted to know how to use this logic in other statements!

Heather

Seattle Repertory Theatre

Thanks, Heather!!!

Thanks, Heather!!!