MySQL Query - help with selection

Question

I am trying to get all users that have no value set for fid = 13, or no value at all in profile_values. I guess this is a simple solution, but I just don't see it. Any help or point in the right direction is welcome.

(Table users and value profiles 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 tried:

 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 should not be, as it matters in fid = 13

---

Thanks for all the incredible quick and qualified answers, greetings!

Answer 1

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

Answer 2

WHERE DOESN'T EXIST (SELECT 1 FROM profile_values ​​pv WHERE pv.uid = u.uid AND pv.fid = 13)

this is actually a slow solution, but I cannot implement any other way

Answer 3

Maybe try

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

?

Your conditions are a bit unclear:

get all users who don’t have a value set for fid = 13, or don’t have a value at all in value_profile

This means that "all users with fid are equal to something except 13 And with some value in the profiles"?

Answer 4

You only need profiles where the value is NOT empty and fid is not 13, right? Thus, this means that if both truths OR, if they are true, skip these entries. Therefore, do not group them together and use 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)