CODEWITHSUNDEEP
mysqlsql
Kyle Cureau
Kyle Cureau

Reputation: 19366

How to treat NULL values as 0 from MySQL

I have two tables joined by user_id. In the query below I'm comparing act_val in table_two to make sure it's in between or equal to min_val and max_val from table_one. But sometimes a user_id in table_one isn't found in table_two. In those cases, I want the act_val to be treated as if it were 0 and qualify where table_one's min_val is lower. Is that possible? How so?

SELECT tb1.id FROM table_one tb1 
INNER JOIN table_two tb2 WHERE 
   tb1.min_val <= tb2.act_val AND tb1.min_val >= tb2.act_val 
   AND tb2.user_id = tb1.user_id 
GROUP BY ad.id;

Upvotes: 2

Views: 6720

Answers (6)

OMG Ponies
OMG Ponies

Reputation: 332571

Use an OUTER join (LEFT in the following example) to get the all the TABLE1 records that may or may not have a matching user_id value in TABLE2. Then construct the WHERE clause to filter down the record set to what you want:

   SELECT tb1.id
     FROM table_one tb1 
LEFT JOIN table_two tb2 ON tb2.user_id = tb1.user_id 
    WHERE tb2.act_val BETWEEN tb1.min_val AND tb1.max_val
       OR tb1.min_val < 0
 GROUP BY tb1.id

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

You need the user_id link in the LEFT JOIN condition (ON clause), and the rest in the WHERE clause (filter). tb2.act_val is treated as 0 when it is missing, using IFNULL. I also fixed up your 2nd condition, it should be     tb1.max_val >=     not     tb1.min_val >=

     SELECT tb1.id
        FROM table_one tb1 
   LEFT JOIN table_two tb2 ON tb2.user_id = tb1.user_id
       WHERE tb1.min_val <= IFNULL(tb2.act_val,0)
         AND tb1.max_val >= IFNULL(tb2.act_val,0)
    GROUP BY tb1.id;

LEFT JOIN:

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:

Upvotes: 7

hsz
hsz

Reputation: 152216

You can use COALESCE function:

WHERE COALESCE(tb1.min_val, 0) <= tb2.act_val COALESCE(tb1.min_val, 0) >= tb2.act_val

Upvotes: 0

oryol
oryol

Reputation: 5248

  1. Replace inner join to left join
  2. Use coalesce(tb2.act_val, 0) instead of tb2.act_val

PS Also - your query looks incorrect because you refers to undefined table ad.

Upvotes: 0

Ted Hopp
Ted Hopp

Reputation: 234795

You can use IFNULL(tbl2.act_val, 0)

Upvotes: 0

mozillanerd
mozillanerd

Reputation: 560

Use the IS_NULL or IS_NOT_NULL operators. Please see http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Upvotes: 0

Related Questions