mysql multiple conditions on the same column-CodePudding

I have a table which, saves the subscriptions of users and guests to topic_names.

Here's the example, now I need to support a bunch of complex queries for example, get all the users who are subscribed to 'so' and 'all' or are subscribed to 'so2' in short : so && (all || so2) I first tried doing it via having clause, but looks like the same column doesn't work in that case, so I came up with this:

select *
from `wp_push_notification_topics`
where exists(select *
             from `wp_push_notification_topics` as `wp_laravel_reserved_0`
             where `wp_push_notification_topics`.`user_id` = `wp_laravel_reserved_0`.`user_id`
               and `topic_name` = 'so'
               and exists(select *
                          from `wp_push_notification_topics`
                          where `wp_laravel_reserved_0`.`user_id` = `wp_push_notification_topics`.`user_id`
                            and `topic_name` = 'all'
                            or exists(select *
                                       from `wp_push_notification_topics` as `wp_laravel_reserved_1`
                                       where `wp_push_notification_topics`.`user_id` = `wp_laravel_reserved_1`.`user_id`
                                         and `topic_name` = 'so2')))

Which works fine.

But even if I change:

and `topic_name` = 'all'

and `topic_name` = 'all22'

I get this result:

Which is clearly the exact as the previous result and therefore wrong! user_id 2 row's must not be included, which means I'm doing something wrong, please help.

CodePudding user response：

You can get the user_ids with aggregation if you set correctly the conditions in the HAVING clause:

SELECT user_id
FROM wp_push_notification_topics
GROUP BY user_id
HAVING SUM(topic_name = 'so') > 0 AND SUM(topic_name IN ('all', 'so2')) > 0;

If you want to restrict the conditions so that the user is not subscribed to any other than 'so', 'all' and 'so2' you can add to the HAVING clause:

AND  SUM(topic_name NOT IN ('so', 'all', 'so2')) = 0

If you want all the rows of the table:

SELECT *
FROM wp_push_notification_topics
WHERE user_id IN (
  SELECT user_id
  FROM wp_push_notification_topics
  GROUP BY user_id
  HAVING SUM(topic_name = 'so') > 0 AND SUM(topic_name IN ('all', 'so2')) > 0
);