Select count(*), where 2 rows with different values exists

Question

I have following table structure and data:

+-----------+-------+
|type | value |
+-----------+-------+
|first_name | john |
+-----------+-------+
|last_name | doe |
+-----------+-------+
|last_name | smith |
+-----------+-------+

I need to get count (in my case it's 1), if table contain data John Doe. Query like:

SELECT COUNT(*) FROM tablename WHERE (type='first_name' AND value='john') AND (type='last_name' AND value='doe')

returns zero count. Don't understand, how to create this combined query.

• danblack: your where clause is evaluated per row. So you cannot have a type=first_name and type=last_name both true at the same time (hence 0 rows). How do you the difference between john doe and john smith? EAV is painful and this is the wrong way to do it.
• user3013494: thank you @danblack, so. there is no way to keep my current structure?
• danblack: There is no relation in the data between first and last name. So it can't be done with the current structure. Structure are there to make queries easy. Change your structure for a first_name, last_name columns and it be very easy. Welcome to DBA stackexchange.
• user3013494: @danblack check my answer

Answer 1

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;