MYSQL - Trouble adding new column via LEFT JOIN

keeeirs10

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!

ST_-Superman-_

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`;

GrantSmith

Try replacing "Remove" with 'Remove' as the double quotes can indicate a column name and not a specific value

What specifically is it returning? Is it just dropping Dummy from your dataset?

keeeirs10

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??

MarkSnodgrass

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.

MarkSnodgrass

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?

keeeirs10

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.

keeeirs10

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`;

MarkSnodgrass

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.

keeeirs10

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?

MichelleH

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 "WHERE Dummy is NULL". If you only want to output rows that are included in lever_inactive_report then I would change your join from a LEFT JOIN to an INNER JOIN.

ST_-Superman-_

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`;