CODEWITHSUNDEEP
mysqlsqlequals
Jhan Karimov
Jhan Karimov

Reputation: 5

mysql not equal operator not working

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?

Upvotes: 0

Views: 1111

Answers (2)

newman
newman

Reputation: 2719

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

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

Upvotes: 0

DRapp
DRapp

Reputation: 48139

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

Upvotes: 2

Related Questions