Search code examples
kettlepentaho-data-integrationpdi

Applying Left Join in Pentaho

I'm try to create Transformation and need to merge two Database based on query like that by using Merge Join and I little bit confuse what should i filled in First Step, Second Step to Lookup for that each query format.

Query Format :

SELECT * FROM A a LEFT JOIN B b on a.value=b.value

SELECT * FROM A a LEFT JOIN B b on b.value=a.value
Solution

  • There are various way to do it.

    • Write the sql with the join in the Table input step. Quick an dirty solution if your table are in the same database, but do not tell a PDI expert you did it that way.

    • If you know there is only one B record for each A record, use a Lookup Stream Step. Very, very, very efficient. The Main flow is the A and the lookup step is B.

    • If you have many B records for each A records, use a Join Rows. Don't be afraid, you do not really make a Cartesian product, as you can put your condition a.value=b.value.

    • In the same situation, you can also make a Merge join. The first step is the step you write fist in the sql select statement.