- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
JOIN on some of the columns
I have a table (that is a spitted URL) in the following format:
field1 | field2 | field3 | field4 | field5 | field6 |
aaaaa | 11111 | qqqqq | aaaaaa | tttttt | yyyyyy |
aaaaa | 11111 | cccccc | rrrrrrr | ||
bbbbb | 22222 | rrrrrrrrr | iiiiiiiiiii | vvvvvv | |
ccccc | 22222 | wwwww | ttttttttt | ||
ddddd | 33333 | 444444 | 5555555 |
Ans the other table has only some of the columns:
field1 | field2 | field3 | field4 | Name | Description |
ccccc | 22222 | Mickey | Mouse | ||
aaaaa | 11111 | Pinky | Brain | ||
ddddd | 33333 | 444444 | ZZ | Top |
I need that the rows in the second table with be marched to first one, when the values of the second table have only a "base" values. This is what I expect to get:
field1 | field2 | field3 | field4 | field5 | field6 | Name | Description |
aaaaa | 11111 | qqqqq | aaaaaa | tttttt | yyyyyy | Pinky | Brain |
aaaaa | 11111 | cccccc | rrrrrrr | Pinky | Brain | ||
aaaaa | 11111 | qqqqq | aaaaaa | tttttt | yyyyyy | Pinky | Brain |
aaaaa | 11111 | cccccc | rrrrrrr | Pinky | Brain | ||
bbbbb | 22222 | rrrrrrrrr | iiiiiiiiiii | vvvvvv | ZZ | Top | |
ccccc | 22222 | wwwww | ttttttttt | Mickey | Mouse | ||
ddddd | 33333 | 444444 | 5555555 | ZZ | Top |
| join type=left field1 field2... []
It make sense that when I do a left join it looks for a corresponding values in all fields, and if it's not there... I have no results
How can I solve it?
Thanks
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![SplunkTrust SplunkTrust](/html/@E48BE65924041B382F8C3220FF058B38/rank_icons/splunk-trust-16.png)
It is not clear how you get your expected results.
Why are the first two lines repeated?
Why is bbbbb ZZ Top?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
They are repeated, since it answers the JOIN condition.
aaaaa | 11111 |
Can be applied to:
field1 | field2 | field3 | field4 | field5 | field6 |
aaaaa | 11111 | qqqqq | aaaaaa | tttttt | yyyyyy |
aaaaa | 11111 | cccccc | rrrrrrr |
The only way, I see that it can be done, is by each time to make a another join... something like:
| join type=left max=0 field1 [...]
| join type=left max=0 field1 field2 [...]
| join type=left max=0 field1 field2 field3 [...]
I was looking for more elegant solution
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![SplunkTrust SplunkTrust](/html/@E48BE65924041B382F8C3220FF058B38/rank_icons/splunk-trust-16.png)
This still doesn't explain why bbbbb "joins" with ZZ Top?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You're right. My mistake. Should be NULL
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![SplunkTrust SplunkTrust](/html/@E48BE65924041B382F8C3220FF058B38/rank_icons/splunk-trust-16.png)
search 1
| append [ search 2 ]
| stats values(*) as * by field1 field2
| mvexpand field3
| mvexpand field4
![](/skins/images/53C7C94B4DD15F7CACC6D77B9B4D55BF/responsive_peak/images/icon_anonymous_message.png)