Question 20330 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL NOT EXISTS: Exclude rows where value does not exist for a field (WordPress)

I’m working with the WordPress databases wp_users and wp_usermeta. I want to list users from wp_users who do not have the term "birdie_log" in the column "meta_key" in wp_usermeta.

wp_users

ID Name
1 John
2 Abe
3 Jane
4 Mark

wp_usermeta

ID meta_key
1 wpuf_user_status
2 birdie_log
2 wpuf_user_status
3 birdie_log
3 wpuf_user_status
4 wpuf_user_status

I tried to accomplish this by joining the tables and using MySQL NOT EXISTS:

SELECT * FROM wp_usermeta 
INNER JOIN wp_users ON wp_usermeta.user_id=wp_users.ID 
WHERE NOT EXISTS (
    SELECT wp_usermeta.meta_key 
    FROM wp_usermeta 
    WHERE wp_usermeta.meta_key = 'birdie_log'

I expected to see a list of users who do not have ‘birdie_log’ in the meta_key field, but no users are listed at all. In the example above, I would ideally see John and Mark listed because their meta_key column does not contain the ‘birdie_log’ value anywhere for them in the wp_usermeta table.

Tags:

2

Answers


  1. 0

    The following query using NOT EXISTS shall return you list of users who do not have ‘birdie_log’ in the meta_key field as in the SQLFIDDLE.

    SELECT * 
FROM wp_users 
WHERE NOT EXISTS (
  SELECT * 
  FROM wp_usermeta 
  WHERE wp_usermeta.ID = wp_users.ID 
    AND wp_usermeta.meta_key = 'birdie_log'
);

    Output :

    ID Name
    1 John
    4 Mark
    Login or Signup to reply.
  2. 0

    You don’t need a JOIN. You just need to correlate the EXISTS subquery with the main query.

    SELECT * 
FROM wp_users
WHERE NOT EXISTS (
    SELECT 1
    FROM wp_usermeta 
    WHERE wp_usermeta.meta_key = 'birdie_log'
    AND wp_user_meta.user_id = wp_users.id
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search