Filter by foreign key constraint

One Star

Filter by foreign key constraint

Hi everybody,
I would want to know if it is possible and how to filter rows compared to the foreign key. 
For example, we have 2 tables :
Table : Customer (id_customer, etc..) 
Table : Order (id_order, id_costumer (FK)).
I would like to insert only orders whose "id_customer" exist. 
Thanks a lot in advance!
One Star

Re: Filter by foreign key constraint

Hi,
You have to control before to insert (in tMap).
However, if you don't have much records and the FK is a real constraint on database, you may want to keep the things simple and just push all the records - those for which the customer doesn't exists will be rejected.
Regards,
TRF
One Star

Re: Filter by foreign key constraint

Thanks for your reply but it really didn't help me^^ I have 42 000 rows to insert and I have seen the filter option in tMap component but don't know how to link the filter to the FK.
Someone else please?
One Star

Re: Filter by foreign key constraint

Anybody else to help me?
Thanks in advance!
One Star

Re: Filter by foreign key constraint

So, you need to have the 2 tables in your job and a tMap to join them.

Main row is Orders, lookup is Customer. Use id_customer as the column to join and set the Join as Inner Join.

Hope this helps,
TRF
One Star

Re: Filter by foreign key constraint

Thanks a lot TRF! Your answer helped me because I didn't know it yet but it doesn't resolve my problem; it doesn't give the result I expected.
Maybe I was not clear in my question :
I would want to know if it is possible and how to filter rows compared to the foreign key. 
For example, we have 2 tables :
Table : Customer (id_customer, etc..) 
Table : Order (id_order, id_costumer (FK)).
I would like to insert only orders whose Order.id_customer exist in the table Customer as id_customer.

Can you now help me further?
Thanks a lot in advance!
One Star

Re: Filter by foreign key constraint

That's exactly what this tMap allow. 
On the right side of the tMap configurator, select False for Catch lookup inner join reject.
With this option, only orders associated to a customer will be retrieved on out1 (or row4 in the following capture).
That's what you expect.

Regards,
TRF
One Star

Re: Filter by foreign key constraint

OK, I'm quite confused then because there are 39 201 lines (not 42 000 actually) in the CSV file (before mapping), 38 863 after mapping and only 6 142 rows inserted in the DB. I suppose that logically, 38 863 rows might be injected, isn't it?
Indeed, while checking the data in the DB, I realized that a lot of rows which must be injected was not...
I have tried again by changing Lookup Model and Match Model but no more results.. Anymore help? Smiley Sad
Thanks in advance!
One Star

Re: Filter by foreign key constraint

Share your tMap
One Star

Re: Filter by foreign key constraint

Here it is.
One Star

Re: Filter by foreign key constraint

It looks OK.
Maybe you have rejected rows at insert time.
Did you check this? Can you share a whole picture of your job?
Regards,
TRF
One Star

Re: Filter by foreign key constraint

One Star

Re: Filter by foreign key constraint

You have 38 863 records pushed to the database.
If only only 6142 rows are available in the table at the end, you probably have errors during the insert.
Add a tLogRow after tMysqlOutput.
In the tMysqlOutput component, go to Advanced setting and uncheck "Extend insert" (Insertions étendues).
Right click on tMysqlOutput then Row, Rejects and click on tLogRow.

Arrange tLogRow at your convenience (table for example) then Run.
You shoud have the rejected records on the console with the according error messages.
Regards,
TRF
One Star

Re: Filter by foreign key constraint

Once more, thanks for helping TRF but I don't have the option you metionned as you can see below Smiley Sad

                                                                        
 
One Star

Re: Filter by foreign key constraint

What when you right click on tMysqlOutput component?
FYI, I'm on TOS 6.1.1. Don't have checked with 6.2.1 which is yours.
One Star

Re: Filter by foreign key constraint

Okay, I had to uncheck 'Die on error' in order to drag a reject row. The result is the same because I have no error Smiley Sad
Fed up^^'

That said, I had errors with outer join then I think that I can try with outer join, and logging rejecting rows...                                                                           
 
One Star

Re: Filter by foreign key constraint

OK, I tried it, that logged the faulty lines but always only 6 142 rows inserted ><
One Star

Re: Filter by foreign key constraint

How many rows are logged? (it should be 38 863 - 6 142 = 32 721)
What is the error message(s)?
One Star

Re: Filter by foreign key constraint

The error is that the foreign key constraint fails and I suppose that data were overwritten because I tried to truncate table and then "insert" in Talend instead of "insert or update", 38 863 rows have been inserted.
One Star

Re: Filter by foreign key constraint

Donc tout va bien ?
One Star

Re: Filter by foreign key constraint

I wanted to filter rows basedon foriegnkey constraints. I'm using posgresql DB. SO, using inne rjoin I'm able to filter the rows based on constraint. But I also need the list of rejected rows. SO to see the reject rows, I'm using ROw---> Rejectedrows  from tpostgresqloutput option. But I'm unable to get the records. 

One Star

Re: Filter by foreign key constraint

Please tell how to capture the rejected records from db of foreign key constraints
One Star

Re: Filter by foreign key constraint

Please, see post #13
Regards,
TRF
One Star

Re: Filter by foreign key constraint

I used the same flow pretty much and I dint find the "Extended Insert" Option for the tPostgresqlOutput.. Can you please help on this how to capture the rejected records.
One Star

Re: Filter by foreign key constraint

I think that you just have to uncheck "die on error", right click "rejects" and then link it to the tLogRow component for example.
One Star

Re: Filter by foreign key constraint

Yes, thats what I have done but still not giving the expected output. Can you please check the screenshot I pasted above.
Thanks
One Star

Re: Filter by foreign key constraint

I see, can you share your tMap? Although I'm not an expert, I'll try to help you. Did you use an outer or an inner join?
One Star

Re: Filter by foreign key constraint

attached is the tmap. Please refer and help on this.

One Star

Re: Filter by foreign key constraint

I already looked at it Vishnu, I need your tMap now.
One Star

Re: Filter by foreign key constraint

attached is the tmap. Please refer and help on this.

415061/mini_blob_20170207-1310.png

Yes, I already answered it; in the right part of your tMap, you have to check "Catch lookup inner join reject" like that screenshot.php.