MYSQL - Trouble adding new column via LEFT JOIN

Options
keeeirs10
keeeirs10 Member
edited March 2023 in SQL DataFlows

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!

Tagged:

Best Answer

  • ST_Superman
    ST_Superman Domo Employee
    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`;

Answers

  • GrantSmith
    GrantSmith Coach

    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?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • keeeirs10
    keeeirs10 Member

    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
    MarkSnodgrass Coach

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    MarkSnodgrass Coach

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • keeeirs10
    keeeirs10 Member

    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
    keeeirs10 Member

    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
    MarkSnodgrass Coach

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • keeeirs10
    keeeirs10 Member

    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
    MichelleH Coach

    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
    ST_Superman Domo Employee
    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`;

Categories