Is there a way to select normalized data where a value IS NOT present

I have a table from WordPress, wp_commentmeta, where I try to allow users to like comments. It looks like this

+---------+------------+-----------------------+---------------------+
| meta_id | comment_id | meta_key              | meta_value          |
+---------+------------+-----------------------+---------------------+
|      23 |          6 | like                  | 2                   |
|      31 |          8 | like                  | 1                   |
|      32 |          6 | like                  | 1                   |
+---------+------------+-----------------------+---------------------+

I know how to get what comments are liked by user 2

Read More
SELECT * FROM `wp_commentmeta` WHERE meta_key="like" AND meta_value=2

But what I want to know is; Is it possible to build a select statement so that I get only comments NOT liked by user 2 so that the output looks like the following?

+---------+------------+----------+------------+
| meta_id | comment_id | meta_key | meta_value |
+---------+------------+----------+------------+
|      31 |          8 | like     |  -         |
+---------+------------+----------+------------+

Related posts

Leave a Reply

2 comments

  1. Yes. You can use aggregation and a having clause:

    select comment_id
    from wp_commentmeta
    where meta_key = 'like'
    group by comment_id
    having sum(meta_value = 2) = 0;
    

    The sum() in the having clause counts the number of likes where the user is 2. The = 0 says that there are none.

    Note: This will only select comments that are liked but not liked by 2. If you also want comments that are never liked, then you need a list of comments somehow. Your question doesn’t have enough information for this variation.

  2. You can do that with aggregates, but most probably it will work slower. This is an example with NOT EXISTS

     SELECT
        *
     FROM
        wp_commentmeta c
     WHERE
        meta_key = "like"
        AND NOT EXISTS ( SELECT
                            1
                         FROM
                            wp_commentmeta c1
                         WHERE
                            c1.metavalue = 2
                            AND c1.comment_id = c.comment_id
                            AND meta_key = "like" )