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
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 | - |
+---------+------------+----------+------------+
Yes. You can use aggregation and a
having
clause:The
sum()
in thehaving
clause counts the number of likes where the user is2
. 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.
You can do that with aggregates, but most probably it will work slower. This is an example with NOT EXISTS