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 to any 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 to any 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 -
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
fromstate
a joinnational_names
b
on a.Name
= b.Name
anda.Year
= b.Year
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive