mysqlsqljoinifnull

JOIN WHEN NULL (mysql)

I want to join 2 tables, (table A and table B). If table B is NULL, using 0 instead. is that possible to query this in mysql?

Suppose: Table A: 

id        regionID 
123         1        
456         2        
789         3

Table B:

regionId Rate
0        $5
1        $10

The result that I want is: 

id        regionID rate
123         1       $10
456         2        $5
789         3        $5

My query in the the join is basically like this:

Table_a a LEFT join table_b b
ON a.region_id = IFNULL(b.region_id,0)

However, it seems to me that "ifnull" does not give any impact on my query

Solution

  • Try this using a nested query to first find what matches and null the rest. I don't normally use mySQL so my syntax might be off.

    select t.regionID, b.Rate
from 
(select a.regionID, b.regionID as 'b_region'
 from table_a a 
 left join table_b b
      on a.regionID = b.regionID) t
left join table_b b
     on IFNULL(t.b_region, 0) = b.regionID