CODEWITHSUNDEEP
mysqlsqlwordpress
abbott567
abbott567

Reputation: 862

SQL, do not pull user_id if at least one row contains a value

I am having some trouble with an SQL query. We have over 100k users in a wordpress database, and i am trying to extract some data based on the group the user belongs to.

We have two groups, registered and pro. Registered is just every user that is signed up to our site. Pro, is users with a paying subscription. The issue I have, is that the way the WP_Groups plugin works, is by adding users to groups, but not removing them from the registered group. This means in the database, in the wp_groups_user_group table there are duplicate entries for each user. Registered = 1, and Pro = 2 eg:

user_id  |  group_id
   1     |     1
   1     |     2

So, for this particular query, I basically want to pull all of the users EXCEPT the users that are in the pro group. So in the above example I would not want to pull the user with the ID of 1 out. However, if I use WHERE user_id = 2 or WHERE user_id = 1 the user is going to be extracted either way, because two entries have the same user ID.

Is there a way I can basically state to not pull in any user id's when at least one of the values of group id is equal to 2?

I would previously have just pulled all of the users in, and then sifted through them in a loop, but due to the sheer volume of users it is causing time outs before it can finish the loop.

The query looks like this at present:

$query = $wpdb->get_results("SELECT wp_users.id, wp_groups_user_group.group_id
                             FROM wp_users
                             INNER JOIN wp_groups_user_group
                             ON wp_users.id = wp_groups_user_group.user_id
                             WHERE wp_groups_user_group.group_id = 1");

Upvotes: 0

Views: 92

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

You are close here. Oftentimes, a HAVING clause is useful for this sort of filtering

SELECT
  `user_id`,
  MAX(`user_group_id`) AS `max_group_id`
FROM `wp_groups_user_group`
GROUP BY `user_id`
HAVING `max_group_id` = 1

This would determine the maximum group number for each user in the wp_groups_user_group table. ANd do a post-selection filter on the records set to remove any who have a maximum group id > 1 (i.e. they are anything but a basic user). I did not use a join here as it is not necessary just to get a list of user id's. If you needed more info from the user table, obviously you would need a join.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select a.id from
(SELECT wp_users.id, max(wp_groups_user_group.group_id) as max_grp_id
FROM wp_users
INNER JOIN wp_groups_user_group
ON wp_users.id = wp_groups_user_group.user_id
-- WHERE wp_groups_user_group.group_id = 1
group by wp_users.id) a
where a.max_grp_id = 1

The inner query will select the max id per group. So, if atleast one user has an associated group_id = 2, it will be left out based on the outer query condition.

Upvotes: 1

Related Questions