Using multiple indexes

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 )

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

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" )

I believe you can do something similar with

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.

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


That might not work after all... Sorry.

Probably you have to run 2 queries .

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