Not in (select field from)

Matillion ETL
1

Hi All,

 

Just started on Matillion and so far great to work with. I encounter one issue now for which I can't seem to find the answer.

 

table with four columns:

 

field1 - field2 - email - source

 

I have a Transformation job in which I want to do this:

 

Select * from table

where 

source = 'A'

and email not in 

(select email from table

where

source = 'B')

 

It's the "email not in" part that bothers me. If I use an except component, only the email field would be returned, and I need all columns.

 

What is the correct way to approach this ? Of course I can use an SQL component, but if possible, I would like to prevent that.

 

Thanks for your time !

 

Cheers,

Gerard Verbruggen

2

Hello @g.verbruggen​

Welcome to the Matillion Community 👋

A method would be to use a Join component and configure it as a Left Join, where you join on the email field. In the Join Output, include the email field from both tables. Right after the Join, add a Filter component and configure that to filter out rows where the email field from the lookup table is Null or Blank.

That should resolve this for you, do let me know how you get on.

Kind regards, Joe

3

Hi Joe,

 

Thanks a lot ! Sounds logical :o)

 

I will give it a try somewhere this week and get back to you with the results.

 

Much appreciated !

Cheers,

Gerard

 

4

Hi @g.verbruggen​ I don't have answer for you question, I would suggest whatever you make the transformation maximum go with code base the reason is in case future if you have a requirement to migrate the code raw code would be easy.