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
- 10.5K All Categories
- 6 Connect
- 916 Connectors
- 250 Workbench
- 463 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 189 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 172 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive