Using multiple indexes


(sami) #1

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 )

(David Pilato) #2

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


(sami) #3
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" )

(David Pilato) #4

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.


(sami) #5

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


(David Pilato) #6

Hmmmm.

That might not work after all... Sorry.

Probably you have to run 2 queries .


(system) #7

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