Self Join Outer JOIN not working as expected

Reply
Highlighted
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
Looking for the latest Dojo Community solutions? Click on the "NEW SOLUTIONS' widget title or go to https://dojo.domo.com/solutions Click here!