4

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?

Yami Odymel
  • 1,782
  • 3
  • 22
  • 48
  • Possible duplicate of [Need a MySQL query for selecting from a table storing key value pairs](http://stackoverflow.com/questions/4287822/need-a-mysql-query-for-selecting-from-a-table-storing-key-value-pairs) – Hubert Grzeskowiak May 17 '16 at 13:27

5 Answers5

5

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
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Thanks for your help, but an error occurred: `Unknown column 'type' in 'having clause'`, the second table is my real structure, does my question mislead you :)? – Yami Odymel May 17 '16 at 10:14
4

Until @juergen-d fixes his typo: here is 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.

gsamaras
  • 71,951
  • 46
  • 188
  • 305
Fabian N.
  • 3,807
  • 2
  • 23
  • 46
1

You'd need a select over the value number with a subselect over the type or the other way around.

Something like

SELECT commentId FROM comment_datas
WHERE name="type" AND value="post"
AND commentId IN (
    SELECT commentId FROM comment_datas WHERE name="target" AND value=2
);

But seriously, you will run into such problems more often if you leave the schema like that. You're basically having two rows instead of one.

Instead of "name" and "value" better use the actual names and values as column names and values. So remove "name" and "value", and add "type" and "target" as columns. This way you'll have no double entries and you can make the ID unique. Your current schema puts a key-value system on top of a key-value system.

Hubert Grzeskowiak
  • 15,137
  • 5
  • 57
  • 74
0

user this, you will get two records 

SELECT `commentId` FROM `comment_datas ` WHERE (name = 'type' AND value = 'post') OR (name = 'target' AND value = '2') GROUP BY `commentId`
AftabHafeez
  • 173
  • 13
0

try this,

    SELECT 
            `commentId` 
    FROM 
            `comment_datas` 
    WHERE 
            (`name` = 'type'   AND `value` = 'post') OR 
            (`name` = 'target' AND `value` = '2') 
    GROUP BY
            `commentId`
Hytool
  • 1,358
  • 1
  • 7
  • 22