Join Issue

Fadem
Fadem βšͺ️

Good afternoon!

I am having a join issue where only my inner join is working, but I would like to either a left outer join or a full outer join. I am trying to join 417 rows of data to 15 rows of data.

Thank you!

Best Answers

  • GrantSmith
    GrantSmith Indiana πŸ”΄
    Accepted Answer

    Hi @Fadem

    What exactly is your issue? What are you getting currently and what are you expecting? Which tool are you utilizing to do you join? View? Magic? MySQL?

    DataSet Views and Magic ETL allows you to specify the join type in the UI. MySQL you need to specify either LEFT, RIGHT or FULL OUTER before your join to do a non-inner join.

  • GrantSmith
    GrantSmith Indiana πŸ”΄
    Accepted Answer

    LEFT JOIN, RIGHT JOIN and OUTER JOIN will all return NULL values if it can't find a match between your two tables based on your join condition. This website may help explain the differences between the different join types: https://www.w3schools.com/sql/sql_join.asp It's working correctly based on your join columns and condition. What are you expecting to see with your joins?

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟀
    Accepted Answer

    @Fadem if your join keys are two text columns, it is likely a data issue. Extra spaces and capitalization are two common reasons why rows don't match when you think they should. I would use the String Operations tile and trim spaces on both ends of your text. I would also convert the fields to upper case. Then try joining on the commonly formatted columns.

Answers

  • Fadem
    Fadem βšͺ️

    Hi @GrantSmith, I am using magic ETL 1 and getting null columns on either of the two datasets I am using depending on join type when I do a left join the right data set is null and vice versa but when I do a full outer join my smaller data columns are returning null. The only join that's working is the inner join but that leaves me with 7 rows.

  • Fadem
    Fadem βšͺ️

    @GrantSmith I am expecting to at least get the 7 matching rows in all the other joins. I don't know why it's giving me all nulls.

  • Fadem
    Fadem βšͺ️

    @MarkSnodgrass Thank you looks like it was a filter issue.

  • Fadem
    Fadem βšͺ️

    @GrantSmith Thank you for your continues help it was a filter issue.