join two tables with date comparison in where clause MySQL

user095063 · April 2021

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

MarkSnodgrass · April 2021

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

MarkSnodgrass · April 2021

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

jaeW_at_Onyx · April 2021

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

user095063 · April 2021

@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 · June 2023

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

MichelleH · June 2023

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

join two tables with date comparison in where clause MySQL

Best Answer

Answers

Categories