Mismatch with values in Join

[tlunruh]

When I run this query:
index=edi-2 | join type=inner TRACKINGNUMBER [search index=edi | rename TRCK AS TRACKINGNUMBER] | stats count(TRACKINGNUMBER) BY STYPE

I get these results:

STYPE count(TRACKINGNUMBER) 01 2140
08 38
10 284
13 1122
22 539
25 349
29 4
32 236

However, if I add this where clause:
index=edi-2 | join type=inner TRACKINGNUMBER [search index=edi | rename TRCK AS TRACKINGNUMBER | where STYPE=08 OR STYPE=29] | stats count(TRACKINGNUMBER) BY STYPE

I get this:

STYPE count(TRACKINGNUMBER)
08 166
29 4572

WTH?? How is that possible? Shouldn't 08 and 29 match up with the first set of results?? What am I missing with this?

Thanks!

[somesoni2]

With type=inner in join command, you'd see matching records from both the sources (main search and subsearch), hence the result is limited to only the STYPE=08 OR STYPE=29. The joins are very expensive, and you should look for alternative implementation for it.

If you have to use join, use type=left so that all rows from left (main search) will be returned, along with matched ones.

Alternatively, you can use following:

index=edi-2 OR index=edi | eval TRACKINGNUMBER=coalesce(TRACKINGNUMBER, TRCK)
| stats count(TRACKINGNUMBER)  values(index) as indexes BY STYPE

This should give count by STYPE for records in both indexes.
To get records same as | join type=inner, add | where mvcount(indexes)=2 | fields - indexes.
To get records same as | join type=left, add | where NOT mvcount(indexes)=1 AND indexes="edi" | fields - indexes.

[tlunruh]

Thanks @somesoni2. I ran your new search and it gave me different data, but I am still not sure it is right. I am sure I am trying to use Splunk JOINs like SQL (which I am very familiar with) and not understanding the overhead/results. What I am really trying to get (for the first pass), is the STYPE from index=edi for each matching TRACKINGNUMBER in index=edi-2.

index=edi has TRCK and STYPE fields; index=edi-2 has TRACKINGNUMBER and DATE fields. I want to JOIN (or use an alternative) the two indexex on TRACKINGNUMBER/TRCK and return the count of STYPE. Does that make sense?