One Star

How to do one-to-many mapping?

Hi, Friends:
I have one-to-many tables to load, but it only get parent table. For example,
table order:
1 p100
2 p200
table operation
1 1 opx
2 1 opy
3 1 opz
4 2 opi
5 2 opj

I want to use table order as main input and lookup the associated table operation, so I can get all operations. It looks like run query,
select a.*, b.* from order a inner join operation b on a.orderid=b.orderid
How can I do this?
Community Manager

Re: How to do one-to-many mapping?

What's the final result should be like?
Best regards
Talend | Data Agility for Modern Business
Not applicable

Re: How to do one-to-many mapping?

Please see if this can meet your requirements.

One Star

Re: How to do one-to-many mapping?

Hi, THeo:
Thank you. But in the case you pointed, if the file 2 has duplicate ID, then how do you handle it? for example,
id item
1 11
1 55
1 99
Also I want to use parent table as main flow.
Hi Shong, I need to oputput like this,
operId orderId operName orderName
1 1 opx p100
2 1 opy p100
3 1 opz p100
4 2 opi p200
5 2 opj p200

Thanks, Vincent
Not applicable

Re: How to do one-to-many mapping?

Hi Vinshu,
in your first post you wrote something like "I want to use order as main input and want to lookup the operation...". This would mean:
1 p100 opx
1 p100 opy
1 p100 opz
2 p200 opi
2 p200 opj

Looks a bit like what you have just posted ;-) But is there a reason why you don't use the operation table as main and only lookup the name from the order table? Cause this would be exactly what you posted as your desired output...
Concerning your question with the duplicate data (i guess you mean that e.g. ORDERID 1 is opx, opy, and opz?): Have you tried to set up your table like in my screenshot from the linked post (with one table less of course) and does the result really not look as you want it to do?
Then please show the result you get and your tMap setup so we can see what may be the problem.


Re: How to do one-to-many mapping?

In the Mapper (tMap component), you have the capability to join table_operation and table_order (ORDERID will be the KEY).
You have also some join options (InnerJoin or OutterJoin, Unique match, First match, Last match ,All matches).
Please refer to the Talend user guide (User guide) and Talend demo project (embedded into the Studio, in the login Page, push DEMOS button to import this project).
Best regards.