check if data exists in another table based on condition

One Star

check if data exists in another table based on condition

I am pulling in data from a database table(A) using tMSSqlInput with SELECT internalkey, anotherkey, name; this part works.
However after pulling the data I need to see for each row if the internalkey, anotherkey combination exists in
in another db table(B)
if it exists, I want to remove the row from the data I pulled from (A); not deleting the rows in the actual DB.
I need to do some additional transformation after on that data, but that is out of the scope of this question.
I just started talend, if someone can give me some idea and what components I need to accomplish this. =)
thanks
Community Manager

Re: check if data exists in another table based on condition

Hi
Select internalkey, anotherkey, name from table(A) as main flow, select internalkey, anotherkey from table(B), and do inner join on tMap, get the matched rows, that rows will be deleted in table(A), the job looks like:
tMssqlInput(table A)--main----tMap---main--tMssqlOutput(table A)(select delete option in the data on table list)
|
lookup
|tMssqlInput(table B)
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: check if data exists in another table based on condition

thanks
I actually don't want to delete the rows in any of the database.
I just want a list with those duplicates removed, I need to do additional transformation on the list.
Could you provide me some ideas on that.
thanks again
One Star

Re: check if data exists in another table based on condition

I guess the question is when I do a join on the two tables
how do i do a "EXCEPT" instead of a inner join
http://msdn.microsoft.com/en-us/library/ms188055.aspx
thanks
Community Manager

Re: check if data exists in another table based on condition

Hi
If the two tables are in the same database, you can write the query and execute it in a tMssqlInput component instead of a inner join. I am assuming you know how to write a 'except' query.Smiley Wink
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business