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

check if data exists in another table based on condition

_AnonymousUser — Sat, 16 Nov 2024 12:36:45 GMT

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

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

Anonymous — Thu, 20 Oct 2011 08:02:48 GMT

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

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

_AnonymousUser — Thu, 20 Oct 2011 14:48:29 GMT

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

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

_AnonymousUser — Thu, 20 Oct 2011 15:38:49 GMT

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

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

Anonymous — Fri, 21 Oct 2011 09:37:54 GMT

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.
Best regards
Shong