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
-
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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2
Answers
-
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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
@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"1 -
@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!
0
Categories
- 10.5K All Categories
- 3 Connect
- 913 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 183 Visualize
- 249 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 168 Product Ideas
- 1.2K Ideas Exchange
- 9 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive