title: Mysql join with multiple case statements in where clause
body: <p>I have two tables. One is <code>engine4_users</code> and other is <code>engine4_user_fields_values</code>. The <code>engine4_users</code> table contains <code>id, email, password,displayname</code>. It has <code>one to many</code> relation with <code>engine4_user_fields_values</code> table. The <code>engine4_user_fields_values</code> table looks like</p>
  
  <p><img src="https://i.stack.imgur.com/R3JeU.png" alt="enter image description here"></p>
  
  <p>Where <code>item_id</code> is the <code>user_id</code> of the users table. Now as you can see, in this table field_id can have many values too against one item id. </p>
  
  <p>Now I want to select data from both of these table in such a way that some times I need value of <code>value</code> column and sometimes I need to put <code>value</code> column in where condition based on field_id. 
  For example see my query</p>
  
  <pre><code>SELECT 
  `eu`.`user_id`, `eu`.`displayname`, 
  GROUP_CONCAT(
                     CASE WHEN eufv.field_id = 19 THEN eufv.value END
                   ) AS city,
      GROUP_CONCAT(
          CASE WHEN eufv.field_id = 15 THEN eufv.value END
          )AS interests
  
  FROM 
       `engine4_users` AS `eu`
      INNER JOIN 
       `engine4_user_fields_values` AS `eufv` 
      ON 
      eu.user_id = eufv.item_id 
      WHERE 
       (eu.username LIKE '%hameed%' OR eu.email LIKE '%hameed%' OR eu.displayname LIKE '%hameed%') 
     GROUP BY 
  `eu`.`user_id`;
  </code></pre>
  
  <p>This query get data group by user and get values of field_id 15 an 16 as comma separated. Now I want to put more where conditions on this second table to filter out the result. </p>
  
  <p>For example I want to put condition on field_id 6 which is birthdate that age of the person is in some range, and field_id 5 to where value is 3. But when I put following query</p>
  
  <pre><code>SELECT 
  `eu`.`user_id`, `eu`.`displayname`, 
  
  GROUP_CONCAT(
                     CASE WHEN eufv.field_id = 19 THEN eufv.value END
                   ) AS city,
      GROUP_CONCAT(
          CASE WHEN eufv.field_id = 15 THEN eufv.value END
          )AS interests
      FROM 
       `engine4_users` AS `eu`
      INNER JOIN 
     `engine4_user_fields_values` AS `eufv` 
      ON 
     eu.user_id = eufv.item_id 
      WHERE 
      (eu.username LIKE '%hameed%' OR eu.email LIKE '%hameed%' OR eu.displayname LIKE '%hameed%') 
     AND 
     (FLOOR(DATEDIFF(CURDATE(),CASE WHEN eufv.field_id = 6 THEN eufv.`value` END ) / 365) &gt;= '20') 
     AND 
     (FLOOR(DATEDIFF(CURDATE(),CASE WHEN eufv.field_id = 6 THEN eufv.`value` END ) / 365) &lt;= '29') 
    GROUP BY 
  `eu`.`user_id`;
  </code></pre>
  
  <p>This query gives result but in this case I don't get values of columns city and interests. They come empty in resultset. And If I put one more condition in where clause which is </p>
  
  <pre><code>AND ((CASE WHEN eufv.field_id = 5 THEN eufv.`value` END)  = '3') 
  </code></pre>
  
  <p>which is good and map with the record. But in this case the result set comes empty. </p>
  
  <p>I have tried to explain my problem as much as I can. So please guide me how can I write this query or let me know if I am unable to convey my problem.</p>
  
  <p>Best Regards</p>