MySQL: How to show rows Not Having certain value

Question

I have a bit of an issue with the following query.

SELECT su.display_name, su.user_email, sm.meta_value FROM system_users AS su
JOIN system_usermeta AS sm ON su.ID = sm.user_id
WHERE sm.meta_key = 'market';

The above query works fine to show all users and their values who have 'market' set in the meta_key column.

But how can I turn this around? How could I show all the users which don't have the meta_key 'market' associated with their user_ID.

score 1 · Answer 1 · answered Jun 29 '15 at 12:05

1

Try this:

SELECT su.display_name, su.user_email, sm.meta_value FROM system_users AS su
JOIN system_usermeta AS sm ON su.ID = sm.user_id
WHERE sm.meta_key <> 'market';

answered Jun 29 '15 at 12:05

Thanos Markou

2,587
3
25
32

score 1 · Answer 2 · answered Jun 29 '15 at 12:08

I think you need a more complex query for this . . . because you are checking multiple rows. Aggregation is one approach:

SELECT su.display_name, su.user_email
FROM system_users su JOIN
     system_usermeta sm
     ON su.ID = sm.user_id
GROUP BY su.display_name, su.user_email
HAVING SUM( sm.meta_key = 'market' ) = 0;

shA.t · Answer 3 · 2015-06-29T12:14:39.753

I suggest you to use LEFT JOIN because you want to add a filter in the left table and show data from right table, so try this:

SELECT su.display_name, su.user_email, sm.meta_value 
FROM system_users AS su
LEFT JOIN system_usermeta AS sm ON su.ID = sm.user_id
      AND sm.meta_key <> 'market';

If you want to filter users that haven't any meta_key with 'market' try this:

SELECT su.display_name, su.user_email, sm.meta_value 
FROM system_users AS su
LEFT JOIN system_usermeta AS sm ON su.ID = sm.user_id
WHERE NOT EXISTS(
    SELECT 1 
    FROM system_usermeta smi
    WHERE su.ID = smi.user_id AND smi.meta_key = 'market')

score 0 · Answer 4 · answered Jun 29 '15 at 12:12

If you want to check whether sm.meta_key is not 'market', then you can use Thanos Markou's solution

SELECT su.display_name, su.user_email, sm.meta_value FROM system_users AS su
JOIN system_usermeta AS sm ON su.ID = sm.user_id
WHERE sm.meta_key <> 'market';

or, if you want to check that sm.meta_key does not contain 'market', then this is how you can do it:

SELECT su.display_name, su.user_email, sm.meta_value FROM system_users AS su
JOIN system_usermeta AS sm ON su.ID = sm.user_id
WHERE sm.meta_key not like '%market%';

If it might be 'Market', then you can make the text lower case to have a case-insensitive check.

I_am_Batman · Answer 5 · 2015-06-29T18:22:29.077

0

Is a join clause necessary?

SELECT su.display_name, su.user_email, sm.meta_value FROM system_users AS su,system_usermeta AS sm where su.ID = sm.user_id
AND sm.meta_key <> 'market';

edited Jun 29 '15 at 18:22

answered Jun 29 '15 at 18:13

I_am_Batman

895
9
21

Apparently, eliminating join doesn't add to any advantage. http://stackoverflow.com/questions/121631/inner-join-vs-where – I_am_Batman Jun 29 '15 at 18:21

score -1 · Answer 6 · answered Jun 29 '15 at 12:08

-1

Use This

SELECT su.display_name, su.user_email, sm.meta_value FROM system_users AS su
JOIN system_usermeta AS sm ON su.ID = sm.user_id
WHERE sm.meta_key != 'market';

answered Jun 29 '15 at 12:08

Amrut Gaikwad

524
1
3
19

hey down-voter , what is reason of down voting ? Don't just down-vote specify the reason for down-voting – Amrut Gaikwad Jun 29 '15 at 12:18

MySQL: How to show rows Not Having certain value

6 Answers6