Join 2 search - Want to display both common and non-common values

[jayannah]

index=abc earliest=-7d@d latest=-6d@d action=commit_success | stats count as SUCCESS by user

Gives the result:
user SUCCESS
user1 2
user4 10
user20 7
user50 12
user55 4
user60 10

Commit failed for users:
index=abc earliest=-7d@d latest=-6d@d action=commit_failed | stats count as FAIL by user

Gives the result:
user FAIL
user1 3
user5 2
user20 4
user40 1
user50 4
user70 2

Please note, here some users have made successful commit only, some did both success & fail and some did commit fail only.

I want to combine these 2 results.

combine with append:
user SUCCESS FAIL
user1 2
user4 10
user20 7
user50 12
user55 4
user60 10
user1 3
user5 2
user20 4
user40 1
user50 4
user70 2

Here the problem is the user names appears 2 times in row for user who have done both success and fail commits

---

If combine with appendcols:
user SUCCESS FAIL
user1 2 3
user4 10 2
user20 7 4
user50 12 1
user55 4 4
user60 10 2

Here the problem is totally incorrect FAIL count for improper users.

---

if I combine with "join"
It either gives me the common name (by default),
if I use type=inner, the failure committed users only are ignored
if I use type=outer, the success committed user only ignore.

But, I want to all the users who have committed success and failures to be display and only 1 row per user even if the user has done both failure and success.

I want the result to be like this
user SUCCESS FAIL
user1 2 3
user4 10
user5 2
user20 7 4
user40 1
user50 12 4
user55 4
user60 10
user70 2

Please do the needful.

[HiroshiSatoh · Accepted solution]

It is not possible to "FULL OUTER JOIN" in JOIN
How about collecting this result by STATS further?

combine with append:
user SUCCESS FAIL
user1 2
user4 10
user20 7
user50 12
user55 4
user60 10
user1 3
user5 2
user20 4
user40 1
user50 4
user70 2

･･･|stats max(SUCCESS) as SUCCESS,max(FAIL) as FAIL by user
user SUCCESS FAIL
user1 2 3
user4 10
user5 2
user20 7 4
user40 1
user50 12 4
user55 4
user60 10
user70 2