Best way to Join data

CloverDX Platform
1

Hi all,

I’m getting stuck with a JOIn problem and I hope, that anyone can help me with it. I have 3 different data-sources and I want to join the data. One source is a table with replies which depend on comments, one table with comments on posts and one table with posts. The dependence of each is like the following:

Posts (id, messagePost)
|
Comments (id, postsId, messageComment)
|
Replies (id, commentsId, messageReply)

The posts can have comments. The comments can have replies.
If I join the data via Left Outer Join it doesn’t work like I want :slight_smile: The problem is, that it could exists 0 or n- comments for a post as well as 0 or n- replies for a post. For example if I join the comments with the Left Outer Join on the posts and there is one post with a couple of deppending comments the result is only one row with the first depending comment. How do I make a fitting Join to have all data in the result? I hope u understand what I mean. It would be really if someone could help me with that!

Thank you,

Peter

2

Hi Peter,

If I understand correctly, the only thing you should change is the order of input streams. ExtMergeJoin component has master input port and slave input port and this fact has to be taken into account during graph creation. Please find attached example graph. I set it in the way that there could be multiple comments for every post and multiple replies for every comment. So the stream with posts is master in the first join and the stream with comments is slave. In the second join, the stream with comments is master and the stream with replies is slave. I used left outer join and everything works as expected.

I hope you will find my example useful. Have a nice day.

Kind regards,