mysql not equal operator not working

Question

When I try this query:

SELECT * 
FROM sds_posts 
WHERE topic_id = '2439' 
  AND author = ANY (SELECT mid 
                    FROM sds_actions 
                    WHERE whoami = '710' AND type = 'block')
  AND status = '1' 
  AND deleted = '0' 
ORDER BY 
  id ASC 
LIMIT 50

it is working correctly.

But I need this one:

SELECT * 
FROM sds_posts 
WHERE topic_id = '2439' 
  AND author <> ANY (SELECT mid 
                    FROM sds_actions 
                    WHERE whoami = '710' AND type = 'block')
  AND status = '1' 
  AND deleted = '0' 
ORDER BY 
  id ASC 
LIMIT 50

This time query have to select opposite of first query, but it is just select all author. I tried != and also NOT IN, but result is same.

So why? Why does <> not work as expected?

If the "any" works, maybe inverting the inner select would work: SELECT * FROM sds_posts WHERE topic_id='2439' and author = any (SELECT mid FROM sds_actions WHERE whoami<>'710' OR type<>'block') AND status='1' and deleted='0' ORDER by id ASC LIMIT 50 — outlyer
– outlyer, Commented Nov 25, 2014 at 4:07

DRapp · Accepted Answer · 2014-11-25 04:01:56Z

2

I would think that changing

and author = any...
to
and NOT author = any...

would work... But if that does not, then I would try doing as a left-join and looking for null. Since the author is the "mid" from the sds_actions, I would write it as...

SELECT 
      sp.* 
   FROM 
      sds_posts sp
         LEFT JOIN sds_actions sa
            on sp.author = sa.mid
           AND sa.whoami = '710'
           AND sa.type = 'block'
   WHERE 
          sp.topic_id = '2439' 
      AND sp.status = '1' 
      AND sp.deleted = '0' 
      AND sa.mid IS NULL
   ORDER by 
      sp.id ASC 
   LIMIT 50

answered Nov 25, 2014 at 4:01

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

newman · Accepted Answer · 2014-11-25 04:02:20Z

0

You can try change author = any(...) to author IN (...)

and change author <> any(...) to author NOT IN (...)

answered Nov 25, 2014 at 4:02

newman

2,71919 silver badges24 bronze badges

Collectives™ on Stack Overflow

mysql not equal operator not working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related