MySQL query on two tables with conditions

Question

I have two tables: users and users_img.

I need to create one query to select all users from users table that have zip column not empty AND have img column empty in users_images (both tables have user ids: users.id and users_img.user_id so those could be joined).

users

1. id
2. name
3. zip
4. last_time

users_ids

1. id
2. user_id (same as id in users)
3. img

I tried this:

SELECT * FROM `users` JOIN `users_ids` on users.id = users_ids.user_id
 WHERE `zip` != '' AND `img` = '' ORDER BY `last_time` DESC 

with no luck. I know is supposed to be quite simple.

Answer 1

it will also work for you

SELECT * FROM `users` as `u` 
JOIN `users_ids` as `uid` on `u`.`id` = `uid`.`user_id` 
WHERE `u`.`zip` IS NOT NULL 
AND `uid`.`img` IS NULL 
ORDER BY `u`.`last_time` DESC

Answer 2

This worked:

SELECT * FROM `users` as `u` 
LEFT JOIN `users_ids` as `uid` on `u`.`id` = `uid`.`user_id` 
WHERE `u`.`zip` != '' 
AND `uid`.`img` IS NOT NULL 
ORDER BY `u`.`last_time` DESC 

Answer 3

I have modified your query to check for Null values also in OR

Try this:

SELECT * FROM `users` 
LEFT JOIN `users_ids` on users.id = users_ids.user_id 
WHERE   (TRIM(`zip`) != '' OR `zip` is not null)  AND 
(TRIM(`img`) = '' OR `img` is null) ORDER BY `last_time` DESC