Self Join Outer JOIN not working as expected

Reply
Yellow Belt

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.


Accepted Solutions
Yellow Belt

Re: Self Join Outer JOIN not working as expected

Never mind. It is working fine.

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


All Replies
Yellow Belt

Re: Self Join Outer JOIN not working as expected

Never mind. It is working fine.

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

Announcements
Win free lodging at Domopalooza! We want to hear your Domo story, enter our Dojo contest and win up to four nights lodging at Domopalooza. Click here!