kannu kannu - 1 year ago 99
MySQL Question

My SQL query in NOT condition not working

My SQL query in NOT condition not working. (NOT u.paymentmethod=3) Please tell me how to make a query for this perfect. Thanks in advance

SELECT (SELECT COUNT(u.`refered`)
FROM user u
WHERE u.refered = user.user_id
AND u.paymentmethod = 2 and NOT u.paymentmethod=3 ) as ref,
user_id,
paymentmethod
from user
HAVING ref < 6

Answer Source

You are using wrong syntax for not change your query as below:

SELECT (SELECT COUNT(u.`refered`) 
         FROM user u 
         WHERE u.refered = user.user_id 
         AND  u.paymentmethod = 2 and u.paymentmethod != 3  ) as ref,
       user_id,
       paymentmethod 
from user  
HAVING ref < 6 and user.paymentmethod = 2

EDIT

Also your query doesn't need u.paymentmethod != 3 condition as u.paymentmethod = 2 is already there

SELECT (SELECT COUNT(u.`refered`) 
             FROM user u 
             WHERE u.refered = user.user_id 
             AND  u.paymentmethod = 2) as ref,
           user_id,
           paymentmethod 
    from user  
    HAVING ref < 6 and user.paymentmethod = 2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download