Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Which join to take?

  1. #1

    Default Which join to take?

    Easy thing but I simply don't see how to do :-(
    What is the best step to join two tables in the following way:

    Table1:
    ID1; Value2
    1; Color
    2; Size
    3; volume

    Table2:
    ID2; Value2
    1; red
    1; green
    1; blue
    2; small
    2; medium

    Result:
    ID1; ID2; Value1; Value2
    1; 1; Color; red
    1; 1; Color; green
    1; 1; Color; blue
    2; 2; Size; small
    2; 2; Size; medium

    Table 1 ID1 3; volume is ignored as it's not existing in Table 2.

    Thanks
    Regards
    Mark

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    All you need is a classic join in your SQL query Mark, nothing fancy or special about it.

    Code:
    SELECT ID1, ID2, Value1, Value2
    FROM table1, table2
    WHERE ID1 = ID2

  3. #3

    Default

    Yes, absolutely right. That's what I ant to do.

    But I can't find a step to execute SQL - Table1 is coming from a stream and Table2 is a table in my DB.
    Regards
    Mark

  4. #4

    Default

    Hi Mark,

    if your 2 sources come from different SQL servers you can do the join with PDI. The step is called "Merge Join" in section Joins.
    Add 2 "Table input" steps (reading your 2 sources). You have to apply an ORDER BY <id-column> clause cause the join in kettle expect ordered input (mention in the popup message).

    Regards,
    Christoph
    21 is only half the truth

  5. #5

    Default

    Thank you. That's it :-)
    Regards
    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.