title: Inner join with sub query to filter record from pivot table
body: <p>This is my query</p>
  
  <pre><code> SELECT t.*
   FROM `User` `t`
   INNER JOIN (
       SELECT cv.relatedId
       FROM CustomValue cv
       WHERE (cv.customFieldId=11 AND (cv.fieldValue like '%dsasda%')) OR (cv.customFieldId=15 AND (cv.fieldValue like '%1%'))
       GROUP BY cv.relatedId
       HAVING count(*) &gt;= 1
  ) tblcv  ON  tblcv.relatedId = t.id
  </code></pre>
  
  <p>I need to do add a AND CONDITION. i am wondering how to.</p>
  
  <p>This is the data set. </p>
  
  <p><a href="https://i.stack.imgur.com/KTlRF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KTlRF.png" alt="enter image description here"></a></p>
  
  <p>As circled in blue, I need to compare against both records for a particular <code>relatedId</code>.</p>
  
  <p>For example, for related id 4031 I need to check if <code>customFieldId</code> = 11 and <code>fieldValue</code> like <code>%dsasda%</code> and if <code>customFieldId</code> = 15 and <code>fieldValue</code> like '%1%'</p>
  
  <p>So the result of query for this data set should have <code>4031</code> <code>relatedId</code> as an answer but I get all 3 related id result. it seems to be using OR CONDITION.</p>
  
  <p><a href="https://i.stack.imgur.com/YayMI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YayMI.png" alt="enter image description here"></a></p>
  
  <p>When try to use AND condition. i dont get any results. This is with AND condition. </p>
  
  <pre><code>SELECT cv.relatedId
  FROM CustomValue cv
  WHERE (cv.customFieldId=11 AND (cv.fieldValue like '%dsasda%')) AND (cv.customFieldId=15 AND (cv.fieldValue like '%1%'))
  GROUP BY cv.relatedId
  HAVING count(*) &gt;= 1
  </code></pre>