Join twice on a different column (only 2 input datasets)
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):
Screenshot 2 (response):
Best 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.
0
Answers
-
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?
0 -
0
-
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...
0 -
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`0 -
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.
0 -
@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
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive