Self Join Outer JOIN not working as expected
Hi,
I am trying to do Outer Join on the same table. However I am not getting the result that is expected.
I have the following dataset:
Item | Country | Event Date | Event | Event Order |
A | US | Apr 29, 2019 | Out Of Stock | 1 |
A | US | May 13, 2019 | Back In Stock | 2 |
A | US | Oct 14, 2019 | Out Of Stock | 3 |
A | UK | Jan 28, 2019 | Out of Stock | 1 |
A | UK | Feb 04, 2019 | Back IN Stock | 2 |
A | UK | May 06, 2019 | Out of stock | 3 |
I am trying to do a SELF JOIN OUTER join to get the date range for which the Item is Out of Stock or In Stock.
Ideally this is the desired O/P:
Item | Country | Event Order | Status | Start Date | End Date |
A | US | 1 | Out Of Stock | Apr 29, 2019 | May 13, 2019 |
A | US | 2 | Back In Stock | May 13, 2019 | Oct 14,2019 |
A | US | 3 | Out of Stock | Oct 14, 2019 | - |
A | UK | 1 | Out of stock | Jan 28, 2019 | Feb 04, 2019 |
A | UK | 2 | Back in stock | Feb 04, 2019 | May 06, 2019 |
A | UK | 3 | Out of stock | May 06, 2019 | - |
This is my code to achieve the desired o/p above:
SELECT a.`Item`,
a.`Country`,
a.`Event Order`,
a.`Event` as 'Status',
a.`Event Date` as 'Start Date',
b.`Event Date` as 'End Date'
FROM `input_table` a
LEFT JOIN `input_table` b
ON b.`Event Order` = a.`Event Order` + 1 and a.`Item` = b.`Item` and a.`Country` = b.`Country`
However, this is the o/p i am getting:
Item | Country | Event Order | Status | Start Date | End Date |
A | US | 1 | Out Of Stock | Apr 29, 2019 | May 13, 2019 |
A | US | 2 | Back In Stock | May 13, 2019 | Oct 14,2019 |
A | UK | 1 | Out of stock | Jan 28, 2019 | Feb 04, 2019 |
A | UK | 2 | Back in stock | Feb 04, 2019 | May 06, 2019 |
Since this is a LEFT OUTER join, it should fetch all rows from the LEFT TABLE even if it does not find matching rows in the RIGHT TABLE. In this example, Event Order = 3 exists in the LEFT TABLE but Event Order = 4 does not exist in the RIGHT TABLE.
Are my conditions in the joins right? Am I missing out on anything?
Thanks in advance.
Best Answer
-
Never mind. It is working fine.
The rows that I thought were missing were in a different order in the output.
0
Answers
-
Never mind. It is working fine.
The rows that I thought were missing were in a different order in the output.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 98 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 712 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 104 Community Announcements
- 4.8K Archive