MySQL Query - help with selection

Question

What I'm trying is to get all users that don't have a value set for fid = 13 or no value at all in profile_values. I'm guessing this have an easy solution, ...but I just don't see it. Any help or a point in the right direction is greatly appreciated.

(The tables users and profile_values are both from Drupal.)

Simplified values:

uid   name  fid     value
1   user1    1       foo
1   user1    2       foo
2   user2    12      
2   user2    13      1265662514
3   user3    NULL    NULL
4   user4    NULL    NULL

What I was trying:

SELECT u.uid, u.name, pv.fid, pv.value
FROM users u 
LEFT JOIN profile_values pv ON pv.uid = u.uid  
WHERE u.status != 0
AND (pv.fid != 13 OR pv.fid IS NULL) 
GROUP BY u.uid

uid     name      fid     value
1     user1     1       foo
2     user2     12      foo 
3     user3     NULL    NULL
4     user4     NULL    NULL

My problem is user2 which shouldn't be there since it has a value in fid = 13

---

Thanks for all the unbelievable fast and qualified answers, cheers!

Answer 1

Maybe try

AND (pv.fid != 13 OR pv.value IS NOT NULL)

?

Your conditions are little unclear:

get all users that don't have a value set for fid = 13 or no value at all in profile_values

This means "get all users with fid equal to something other than 13 AND with some value in profile_values"?

Answer 2

WHERE NOT EXISTS (SELECT 1 FROM profile_values pv WHERE pv.uid = u.uid AND pv.fid = 13)

this is actually a slow solution but i cannot realize any other way

Answer 3

You want to add an AND clause: AND u.uid NOT IN (select uid from profile_values where fid=13) as bad_uids

Answer 4

You want only the profiles where the value is NOT blank and the fid is not 13, right? So that means if both are true OR if either are true, skip those records. So don't group them together and use the OR. Use and:

WHERE 
u.status != 0
AND 
pv.fid != 13 
AND
pv.fid IS NOT NULL 

OR

WHERE 
u.status != 0
AND 
pv.fid IS NOT (13 OR NULL)