MYSQL - Trouble adding new column via LEFT JOIN
Hello! I am having trouble adding a new column, "Dummy", to my output dataset. Although the query is valid, the output does not produce the "Dummy" column when ran. I am trying to identify rows to remove based on the criteria defined in the LEFT JOIN. See below.
SELECT
A.`Posting Title`,
A.`Weekly Reporting Date`,
A.`Weekly Report Stage`,
A.`Funnel Stage`,
A.`Candidate Link`,
A.`Origin`,
A.`Department`,
A.`Days in Stage`,
A.`Days to Hire`,
A.`Days to Archive`,
A.`Offer Acceptance Date`,
A.`Candidate Journey Start Date`,
A.`Event Date`,
A.`Previous Event Date`,
A.`Previous Opportunity Stage`,
A.`Candidate Name`,
A.`Hiring Manager`,
A.`Posting Created Date`,
A.`Owner`,
A.`Posting Country`,
A.`Posting Location`,
A.`Team`,
A.`Posting Status`,
A.`Archive Reason`,
A.`Candidate Email`,
Bad.`Dummy`
FROM `lever_weekly_khtest` AS A
LEFT JOIN (SELECT `Candidate Name`,
"Remove" AS Dummy
FROM `lever_inactive_report`
WHERE `Candidate Name` IS NOT NULL) AS Bad
ON A.`Candidate Name` = Bad.`Candidate Name`
WHERE Bad.`Dummy` IS NULL;
Thank you!
Best Answer
-
I would try something like this
SELECT A.`Posting Title`, A.`Weekly Reporting Date`, A.`Weekly Report Stage`, A.`Funnel Stage`, A.`Candidate Link`, A.`Origin`, A.`Department`, A.`Days in Stage`, A.`Days to Hire`, A.`Days to Archive`, A.`Offer Acceptance Date`, A.`Candidate Journey Start Date`, A.`Event Date`, A.`Previous Event Date`, A.`Previous Opportunity Stage`, A.`Candidate Name`, A.`Hiring Manager`, A.`Posting Created Date`, A.`Owner`, A.`Posting Country`, A.`Posting Location`, A.`Team`, A.`Posting Status`, A.`Archive Reason`, A.`Candidate Email`, IFNULL(Bad.`Dummy`, 'Keep') as `Dummy` FROM `lever_weekly_khtest` AS A LEFT JOIN (SELECT `Opportunity ID`, "Remove" AS Dummy FROM `lever_inactive_report` WHERE `Archive Reason` IS NOT NULL) AS Bad ON A.`Opportunity ID` = Bad.`Opportunity ID`;
2
Answers
-
Try replacing
"Remove"
with'Remove'
as the double quotes can indicate a column name and not a specific valueWhat specifically is it returning? Is it just dropping Dummy from your dataset?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
It is returning every column except for the Dummy column! The single ticks did not work sadly.
Even weirder, The logic built out in the LEFT JOIN is applying to some candidates but not all, even though their names appear on the lever_inactive_report table. Any thoughts??
0 -
I'm not sure how your query is returning any results because you are hard-coding a value of Remove in your Bad table, which will make every row in the bad table a have a value. You are then wanting records from the bad table where Dummy is null, but you just populated the value of Remove in every row in that table.
I would suggest not using the dummy column and then having your final where clause be where Bad.CandidateName IS NULL to get the missing rows.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
My apologies, since you are doing a left join you would get results, so ignore my first comment. Are you saying you don't see the column heading? Or you don't see any values?
I have seen where the preview window doesn't always refresh when you add another column. Does it show up when you save and run the ETL and you view the dataset in the data center?
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
No worries! The Dummy column does not show up when I run the ETL and view the dataset output.
When I am transforming and previewing the output when editing my dataflow with MySQL, the column is there.
0 -
Updated Query (also not working properly):
SELECT
A.`Posting Title`,
A.`Weekly Reporting Date`,
A.`Weekly Report Stage`,
A.`Funnel Stage`,
A.`Candidate Link`,
A.`Origin`,
A.`Department`,
A.`Days in Stage`,
A.`Days to Hire`,
A.`Days to Archive`,
A.`Offer Acceptance Date`,
A.`Candidate Journey Start Date`,
A.`Event Date`,
A.`Previous Event Date`,
A.`Previous Opportunity Stage`,
A.`Candidate Name`,
A.`Hiring Manager`,
A.`Posting Created Date`,
A.`Owner`,
A.`Posting Country`,
A.`Posting Location`,
A.`Team`,
A.`Posting Status`,
A.`Archive Reason`,
A.`Candidate Email`,
Bad.`Dummy`
FROM `lever_weekly_khtest` AS A
LEFT JOIN (SELECT `Opportunity ID`,
"Remove" AS Dummy
FROM `lever_inactive_report`
WHERE `Archive Reason` IS NOT NULL) AS Bad
ON A.`Opportunity ID` = Bad.`Opportunity ID`;
0 -
If the above select statement is what is in your output dataset, I might try moving that statement to be a new transform in your ETL and then just do SELECT * FROM last_transform_name (whatever you name the new transform) in your output dataset. Perhaps that will shake it loose.
I tend to not put much logic in the output dataset, but have it be very basic and have the transforms do any joining etc.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
I updated my output dataset query to make it simple! Now I have the Dummy variable pulled in, but the logic built out in the LEFT JOIN is applying to some rows, but not all in the final output dataset. Any ideas?
0 -
Hi @keeeirs10 are you saying that the
Dummy
column is only populating "Remove" in some rows, but not all? That's likely because your updated query (the second one you posted) removed the "WHEREDummy
is NULL". If you only want to output rows that are included inlever_inactive_report
then I would change your join from a LEFT JOIN to an INNER JOIN.0 -
I would try something like this
SELECT A.`Posting Title`, A.`Weekly Reporting Date`, A.`Weekly Report Stage`, A.`Funnel Stage`, A.`Candidate Link`, A.`Origin`, A.`Department`, A.`Days in Stage`, A.`Days to Hire`, A.`Days to Archive`, A.`Offer Acceptance Date`, A.`Candidate Journey Start Date`, A.`Event Date`, A.`Previous Event Date`, A.`Previous Opportunity Stage`, A.`Candidate Name`, A.`Hiring Manager`, A.`Posting Created Date`, A.`Owner`, A.`Posting Country`, A.`Posting Location`, A.`Team`, A.`Posting Status`, A.`Archive Reason`, A.`Candidate Email`, IFNULL(Bad.`Dummy`, 'Keep') as `Dummy` FROM `lever_weekly_khtest` AS A LEFT JOIN (SELECT `Opportunity ID`, "Remove" AS Dummy FROM `lever_inactive_report` WHERE `Archive Reason` IS NOT NULL) AS Bad ON A.`Opportunity ID` = Bad.`Opportunity ID`;
2
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