join two tables with date comparison in where clause MySQL

I have two tables that I need to join based on the date differences. Table a looks like this:

```

Date    Partner    Revenue

12/01/20  Cosmo      10

12/01/20  Esquire     5

```

Table b looks like this: 

```

Date    Partner    Revenue

12/01/20  Cosmo      10

12/01/20  Esquire     5

12/02/20  Cosmo      20

12/02/20  Esquire     25

```

I need to grab everything from table **b**, which is later than the last (max) date in table **a**.

My desired output of table c is:

```

Date    Partner    Revenue

12/01/20  Cosmo      10

12/01/20  Esquire     5

12/02/20  Cosmo      20

12/02/20  Esquire     25

```

This is what I have tried

```

select a.`Date`,

a.`Partner`,

a.`Revenue`

from a

inner join b

where b.`Date` > (select(max(`Date`))) from a

```

Error message: 


> The database reported a syntax error: You have an error in your SQL

> syntax; check the manual that corresponds to your MySQL server version

> for the right syntax to use near 'from a' at line 6

Best Answer

  • MarkSnodgrass
    Answer ✓

    The error is because you are missing the ON clause in your join statement. A typical JOIN query would look like this:

    SELECT *
    FROM a 
    JOIN b ON a.id = b.id
    


    However, based on what you are describing, you don't need a JOIN clause at all. You can accomplish it by doing this:

    SELECT *
    FROM b
    WHERE b.date > (SELECT MAX(Date) FROM a)
    

    I

    **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.

Answers

  • MarkSnodgrass
    Answer ✓

    The error is because you are missing the ON clause in your join statement. A typical JOIN query would look like this:

    SELECT *
    FROM a 
    JOIN b ON a.id = b.id
    


    However, based on what you are describing, you don't need a JOIN clause at all. You can accomplish it by doing this:

    SELECT *
    FROM b
    WHERE b.date > (SELECT MAX(Date) FROM a)
    

    I

    **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.
  • @user095063

    @MarkSnodgrass is spot on in his SQL.

    The key difference between JOIN + ON versus subquery + WHERE is that if there was additional data of interest from the subquery, you wouldn't be able to include it in the SELECT clause.


    But what's your use case? solving SQL syntax is easy... but I have questions about what you're trying to accomplish and whether JOIN'ing in a SQL dataflow is the appropriate choice for meeting the data requirement.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • @jaeW_at_Onyx Thank you for your response. I have two tables, one with historical data and the other dataset that gets updated every day and grabs the last 4 days. Because the data ingestion is inconsistent, I have to check what is the latest date that we have in historical and append whatever we don't have.

    With the help of @MarkSnodgrass I was able to perform this date filtering and created a new query where I just unioned all historical + filtered data.


    Thank you both very much!

  • Ajita
    Ajita Member

    I am trying to create a SQL Transform but doesn't seem to work .Please help me understand what is not correct

    SELECT a.Name, a.Id, a.state,b.Year,b.Gender
    from state a join
    national_names b
    on a.Name = b.Name anda.Year = b.Year

  • @Ajita It looks like you aren't specifying what kind of join you want to do. You will need to add either LEFT, RIGHT, INNER, or FULL OUTER in front of the word JOIN.