Yami Odymel Yami Odymel - 6 months ago 9
SQL Question

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

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

Until @juergen-d fixes his typo: here the corrected version:

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

Explanation:

sum accepts a aggregate expression besides column names so the idea is to sum the entries with name = type and value = post
By having the sum to be greater than 0 the select returns only the rows of the group by that satisfy the aggregate expression - same goes for target

Comments