MySQL query with column of null values

Question

I have this query:

 user = self.db.query("SELECT DISTINCT u.id, u.name, n.network_id, n.perm \ 
FROM users as u LEFT OUTER JOIN nets_permissions as n \ 
ON u.id = n.user_id WHERE u.id!=%s", int(usr_id))

The column 'perm' (permission) is an int value, that can be a value like 1, 2, 3, ecc. or a null value. I want a query like this:

 user = self.db.query("SELECT DISTINCT u.id, u.name, n.network_id, n.perm \
FROM users as u LEFT OUTER JOIN nets_permissions as n \
ON u.id = n.user_id WHERE u.id!=%s" AND n.perm!=3, int(usr_id))

So, I want delete from the query the records with perm=3.

I thinked that I can retrieve the id of the user with perm=3 in this way:

self.lock_tables("read", ['nets_permissions'])
        admin = self.db.query("SELECT user_id FROM nets_permissions \
                               WHERE network_id=%s AND perm=3", nid)
        self.unlock_tables()

and then I could write

 self.lock_tables("read", ['users as u', 'nets_permissions as n'])
   user = self.db.query("SELECT DISTINCT u.id, u.name, n.network_id, n.perm \
FROM users as u LEFT OUTER JOIN nets_permissions as n \
ON u.id = n.user_id WHERE u.id!=%s AND u.id!=%s", int(usr_id), int(admin))
            self.unlock_tables()

But this doesn't work....

Any idea?

Thank you

podiluska · Accepted Answer · 2012-09-05 09:56:53Z

2

Try this

SELECT DISTINCT u.id, u.name, n.network_id, n.perm 
FROM users as u 
LEFT OUTER JOIN nets_permissions as n 
ON u.id = n.user_id 
AND n.perm<> 3 
WHERE u.id!=%s

answered Sep 5, 2012 at 9:56

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

2 Comments

podiluska Over a year ago

@IliaRostovtsev Give them time - there's a ten minute delay restriction, I think :)

sharkbait Over a year ago

No no I have to take 10 minute after I can accept the answer ;) thank you @podiluska

Collectives™ on Stack Overflow

MySQL query with column of null values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related