I’m working with a MySQL database that contains comments related to orders. I want to find all the comment_post_IDs that have more than one comment with the phrase “Dintero Order created with” and also have at least one comment with the phrase “The Dintero order”. I tried using this query: select comment_post_ID,comment_content, COUNT (comment_post_ID) from wpvo_comments where comment_content like “Dintero Order created with%” AND comment_content LIKE “The Dintero order%” GROUP BY comment_post_ID HAVING COUNT (comment_post_ID)>1 ORDER BY `wpvo_comments`.`comment_post_ID` DESC; But it didn’t work as expected. It returned zero results, even though I know there are some comment_post_IDs that match my criteria. For example, here is a screenshot of one such comment_post_ID: [insert image link] How can I modify my query to get the correct results? What am I doing wrong in my current query? Any help would be appreciated. Thanks in advance.
The problem with your current query is that you are using the AND operator to combine two conditions that are mutually exclusive. You are asking for comments that contain both “Dintero Order created with” and “The Dintero order”, but these are two different phrases that appear in different comments. Therefore, your query will never return any results.
What you need to do is to use a subquery to find the comment_post_IDs that have more than one comment with “Dintero Order created with”, and then join it with the original table to filter out the ones that also have at least one comment with “The Dintero order”. Here is an example of how you can do that:
select c.comment_post_ID,c.comment_content from wpvo_comments c
join (
select comment_post_ID from wpvo_comments
where comment_content like “Dintero Order created with%”
group by comment_post_ID
having count(comment_post_ID) > 1
) d on c.comment_post_ID = d.comment_post_ID
where c.comment_content like “The Dintero order%”
order by c.comment_post_ID desc;
This query will first find the comment_post_IDs that have more than one comment with “Dintero Order created with” in the subquery d, and then join it with the original table c to filter out the ones that also have at least one comment with “The Dintero order”. The result will be a list of comment_post_IDs and comment_contents that match your criteria, ordered by comment_post_ID in descending order.
I hope this helps you solve your problem. If you have any questions or feedback, please let me know.