"SELECT .. WHERE .. AND .." (two conditions) for a key-value table?

Question

What I'm Trying To Do

I am trying to get the commentId by asking type = x and target = x,

normally (THIS IS A EXAMPLE), the structure of the table should looks like this:

+-----------+-------+--------+
| commentId | type  | target |
+-----------+-------+--------+
|         1 | post  |      2 |
|         2 | post  |      8 |
|         3 | video |      6 |
+-----------+-------+--------+

and in this situation, I can use this query to get the commentId:

SELECT `commentId` FROM `comment_datas` WHERE type = 'post' AND target = '2'

---

Real Problem

But this is the real structure of the table (with the key-value design):

+-----------+--------+-------+
| commentId |  name  | value |
+-----------+--------+-------+
|         1 | type   | post  |
|         1 | target | 2     |
|         2 | type   | post  |
|         2 | target | 8     |
|         3 | type   | post  |
|         3 | target | 6     |
+-----------+--------+-------+

now I don't know how to get the commentId by the query which I wrote above, any thoughts?

Answer 1

Group by the commentId. Then you can filter those groups having at least one record with the conditons you want

SELECT commentId 
FROM comment_datas 
GROUP BY commentId
HAVING sum(name = 'type' and value = 'post') > 0 
   AND sum(name = 'target' and value = '2') > 0