Join twice on a different column (only 2 input datasets)

John-Peddle
John-Peddle Contributor
in Magic ETL

I've looked but unable to find anything to indicate it's been asked or that it's ever been addressed but we're looking to join an input dataset 2x (same column) to the other input dataset but on a different column each time; is this possible?

 

Example:

Table 1: Conversation Assignee and Conversation Rating Assignee (Dataset 1)

Table 2: Agent Name (Dataset 2)

 

We want to left join Agent Name to Conversation Assignee, and then left join on Agent Name again, but this time with Conversation Rating Assignee.

 

This is how we've joined the first instance, which seems to work fine:

SELECT a.*,b.*
FROM
`Conversations` a
LEFT JOIN `Employee Roster` b ON a.`Conversation Assignee` = b.`Agent Name`*

  

The closest example we found online to what we want to accomplish is slightly different, and we haven't been able to figure out what needs to done to make this work (up until a month ago, I worked w/ETL but have been trying to better understand and use MySQL since it's going to benefit the company long term as well as myself!

 

Screenshot 1 (request for assistance): 

Screen Shot 2018-10-17 at 9.49.59 AM.png

Screenshot 2 (response):
Screen Shot 2018-10-17 at 9.48.32 AM.png

Best Answer

  • NewsomSolutions
    NewsomSolutions Domo Employee
    Answer ✓

    Also if for some reason the mysql in domo doesn't like the last option, you could create a new column in that table employee roster table by doing something like this "select agent name, agent name two from employee roster...and then on your join you'd be technically joining two seperate columns...but the values would be the same.

Answers

  • NewsomSolutions
    NewsomSolutions Domo Employee

    Maybe my SQL is weak, but why wouldn't 

     

    SELECT a.*,b.*
    FROM
    `Conversations` a
    LEFT JOIN `Employee Roster` b ON a.`Conversation Assignee` = b.`Agent Name`
    LEFT JOIN `Employee Roster` b ON a.`Conversation Rating Assignee.` = b.`Agent Name`

     

    work for you?

  • NewsomSolutions
    NewsomSolutions Domo Employee

    I find this helpful too 

     

    https://www.piliapp.com/mysql-syntax-check/

     

     

  • John-Peddle
    John-Peddle Contributor

    Thanks for the reply, @NewsomSolutions, and probably should've added that the solution you provided was already attempted and returned an error message which I really don't fully understand (The database reported a syntax error. Not unique table/alias: 'b').

     

    I'll check out the link you provided, maybe that can shed some light on the issue and why the transform won't work; I thought it would be fairly simple but it's proving to be a bit more involved than anticipated! Thanks again for your help...

  • NewsomSolutions
    NewsomSolutions Domo Employee

    Then try 

     

    SELECT a.*,b.*
    FROM
    `Conversations` a
    LEFT JOIN `Employee Roster` b ON a.`Conversation Assignee` = b.`Agent Name`
    and a.`Conversation Rating Assignee.` = b.`Agent Name`

     

  • NewsomSolutions
    NewsomSolutions Domo Employee
    Answer ✓

    Also if for some reason the mysql in domo doesn't like the last option, you could create a new column in that table employee roster table by doing something like this "select agent name, agent name two from employee roster...and then on your join you'd be technically joining two seperate columns...but the values would be the same.

  • John-Peddle
    John-Peddle Contributor

    @NewsomSolutions, this one worked but at first returned a syntax error message (saw that a . was added to `Conversation Rating Assignee.`, but it DOES work as hoped! Figured it would be easy, what's confusing is why it's so difficult to find this solution in a search result; maybe it was the wording used in the search?!? Thanks for you assistance! 

     

    **UPDATE: sorry, spoke too soon and just now realized that it only shows a single Agent Name column, which wouldn't be correct when the Conversation Assignee and Conversation Rating Assignee names are different (that's why we need to join it twice, the rating doesn't always go to the assigned agent).

     

    I believe your alternate option will be the way to go, by adding an additional column (Agent Name 2) and joining on each, i.e. 2 different columns each time, whether in a single transform or two separate transforms. Thanks again for your help, @NewsomSolutions

Categories