Self Join Outer JOIN not working as expected

Prajju

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.

Prajju

Never mind. It is working fine.

The rows that I thought were missing were in a different order in the output.

Prajju

Never mind. It is working fine.

The rows that I thought were missing were in a different order in the output.