title: Elastic Search - How to query for one to many table joins with AND
body: <p>I am new to elastic search. May I know if the following query could be achieved by Elastic search? If so, what should the query string look like?</p>
  
  <p>Input: a sql like sentence. The Sentence is:  "(A and B) and (C and D)"</p>
  
  <p>A, B, C, D are field comparison. E.g, A ->   field "Staff Name" has Value equal to "John".</p>
  
  <p>E.g, There are 2 tables. Employee Table and Task Table.</p>
  
  <p><strong>Employee Table</strong></p>
  
  <p>StaffName: John      </p>
  
  <p>StaffName: Mary</p>
  
  <p><strong>Task Table</strong></p>
  
  <p>TaskName: JohnTaskA,  StaffName: John</p>
  
  <p>TaskName: JohnTaskB,  StaffName: John</p>
  
  <p>TaskName: MaryTaskA,  StaffName: Mary</p>
  
  <p>So in traditional DB,  John could link up to JohnTaskA and JohnTaskB as a 1 to many relationship.</p>
  
  <p>My expected behaviour for the query behaviour (A and B) and (C and D)  is as below:</p>
  
  <p>After (A and B) query, assume only return one record.  John -> JohnTaskA</p>
  
  <p>and after (C and D) query, assume only return one record. John -> JohnTaskB</p>
  
  <p>Then, the following query
  (A and B) and (C and D) 
  should return no value since "John -> JohnTaskA" not match with "John -> JohnTaskB" actually.</p>
  
  <p>In opposite, if (C and D) query, only return one record out as John -> JohnTaskB</p>
  
  <p>Then the following query
  (A and B) and (C and D) 
  could return the record John back.</p>
  
  <p>But I cannot acheive this expect behaviour by elastic search query. In my first attempt I use nestedQuery to handle the Task Table's query, and could successfully achieve the multiple column checking in the nested table.</p>
  
  <p>E.g if A ->  Task.TaskName = JohnTaskA,    B -> Task.StaffName = John, then A and B could return a record.</p>
  
  <p>But if A -> Task.TaskName = JohnTaskA,    B -> Task.StaffName = Mary, then A and B could not return any record.</p>
  
  <p>But I found if in this case</p>
  
  <p>(A and B) and (C and D)</p>
  
  <p>I could not use the middle "and" to link up two nested query result together. It doesn't return the expect behaviour.</p>
  
  <p>In case the SQL sentence cannot be changed, may I know how could elastic search achieve this query? or I need to use other ways like parent-child relationships?</p>
  
  <p>Thanks in advance for your help!</p>
  
  <hr>
  
  <p>For (A AND B) AND (C AND D), </p>
  
  <p>(A AND B) return the child record with id=c1. </p>
  
  <p>(C AND D) return the child record with id=c2. </p>
  
  <p>Even they share the same parent record (say parent record id =p1), since c1 != c2, (A AND B) AND (C AND D) returns no record if query by SQL, But using elastic search, it still return the record p1. </p>