Using multiple indexes

aliyesami · February 8, 2018, 4:35pm

I want to implement the following query structure where two tables are involved, I could bring each table into a separate index but how can I join the info as follows ?

select * from tab1 
    where  name='john' 
    and NOT EXITS 
    	(select * from tab2 where age < 40 )

dadoonet · February 8, 2018, 4:47pm

You can't do join at search time (unless you use the parent/child mechanism which comes with some tradeoffs).

aliyesami · February 8, 2018, 7:54pm

what about subquery like this ?  can this be implemented in elasticsearch ?

SELECT LastName, FirstName 
FROM dbo.Employees
WHERE City in 
 ( SELECT City
 FROM Employees
 WHERE LastName="Davolio" 
 AND FirstName="Nancy" )

dadoonet · February 8, 2018, 8:37pm

I believe you can do something similar with https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html

Basically, search for LastName/FirstName with a query, compute a terms agg on the city then add a top hits agg as a sub aggregation.

That might work.

aliyesami · February 8, 2018, 9:09pm

I am not understanding the role of "top hits aggregation"

dadoonet · February 8, 2018, 9:21pm

Hmmmm.

That might not work after all... Sorry.

Probably you have to run 2 queries .

system · March 8, 2018, 9:21pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.