Select count(*), where 2 rows with different values exists
I think I found the solution for this case. My solution is to use SUM() aggregation with IF() condition. The final logic is substitute COUNT(*) to boolean - have rows or empty query. This query will return results only if it match both conditions:

SELECT SUM(IF((value='john' AND type='first_name') OR (value='doe' AND type='last_name'), 1, 0)) AS cnt FROM tablename HAVING cnt=2;


If I want to select all data where first name is John and last name is Doe OR last name is Smith, query will be:

SELECT sum(IF((value='john' AND type='first_name') OR (value='doe' AND type='last_name'), 1, 0)) AS first_last, SUM(IF(value='smith' AND type='last_name', 1, 0)) AS last FROM tablename HAVING first_last=2 OR last=1;