https://community.splunk.com/t5/Splunk-Search/Equivalent-of-SQL-WHERE-IN-Subquery-clause/m-p/446535#M126680 <P>have you considered a join instead of a subquery it might be easier</P> Tue, 17 Jul 2018 22:22:40 GMT kiamco 2018-07-17T22:22:40Z https://community.splunk.com/t5/Splunk-Search/Equivalent-of-SQL-WHERE-IN-Subquery-clause/m-p/446534#M126679 <P>Hello,</P> <P>I am looking for the equivalent of performing SQL like such:</P> <P>SELECT transaction_id, vendor<BR /> FROM orders<BR /> WHERE transaction_id IN (SELECT transaction_id FROM events).</P> <P>I am aware this a way to do this through a lookup, but I don't think it would be a good use case in this situation because there are constantly new transaction_id's generated and several thousand of them within a small timeframe, as well as my goal to create a timechart report.</P> <P>As of right now I can construct a list of transaction_ids for orders in one search query and a list of transaction_ids for events in another search query, but my ultimate goal is to return order logs that have transaction_ids shared with the transaction_ids of the events log. Any help is greatly appreciated, thanks!</P> Tue, 29 Sep 2020 20:30:08 GMT https://community.splunk.com/t5/Splunk-Search/Equivalent-of-SQL-WHERE-IN-Subquery-clause/m-p/446534#M126679 ixixix_spl 2020-09-29T20:30:08Z https://community.splunk.com/t5/Splunk-Search/Equivalent-of-SQL-WHERE-IN-Subquery-clause/m-p/446535#M126680 <P>have you considered a join instead of a subquery it might be easier</P> Tue, 17 Jul 2018 22:22:40 GMT https://community.splunk.com/t5/Splunk-Search/Equivalent-of-SQL-WHERE-IN-Subquery-clause/m-p/446535#M126680 kiamco 2018-07-17T22:22:40Z https://community.splunk.com/t5/Splunk-Search/Equivalent-of-SQL-WHERE-IN-Subquery-clause/m-p/446536#M126681 <P>You may want to have a look at the "Splunk SPL for SQL Users" manual (<A href="http://docs.splunk.com/Documentation/Splunk/7.1.2/SearchReference/SQLtoSplunk">http://docs.splunk.com/Documentation/Splunk/7.1.2/SearchReference/SQLtoSplunk</A>).</P> <P>See if this gets you started.</P> <PRE><CODE>index=orders vendor=* [ search index=events transaction_id=* | fields transaction_id | format ] </CODE></PRE> Wed, 18 Jul 2018 01:46:59 GMT https://community.splunk.com/t5/Splunk-Search/Equivalent-of-SQL-WHERE-IN-Subquery-clause/m-p/446536#M126681 richgalloway 2018-07-18T01:46:59Z https://community.splunk.com/t5/Splunk-Search/Equivalent-of-SQL-WHERE-IN-Subquery-clause/m-p/446537#M126682 <P>@kiamco can you give an example of how you would join two searches by a single field?</P> Wed, 18 Jul 2018 23:57:19 GMT https://community.splunk.com/t5/Splunk-Search/Equivalent-of-SQL-WHERE-IN-Subquery-clause/m-p/446537#M126682 ixixix_spl 2018-07-18T23:57:19Z https://community.splunk.com/t5/Splunk-Search/Equivalent-of-SQL-WHERE-IN-Subquery-clause/m-p/446538#M126683 <P><a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/126393">@ixixix_spl</a> so I assuming that transaction_id is unique</P> <PRE><CODE> first query | table transcation_id | join type=left transcation_id [|search second query |table orders] </CODE></PRE> <P>something like that but it gets a bit complicated depending on the fields you want to join but try it out see if it works</P> Tue, 29 Sep 2020 20:33:17 GMT https://community.splunk.com/t5/Splunk-Search/Equivalent-of-SQL-WHERE-IN-Subquery-clause/m-p/446538#M126683 kiamco 2020-09-29T20:33:17Z