2

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

hnandarusdy
  • 412
  • 5
  • 19
  • You might want to include data that have NULL value. – FanoFN Apr 26 '19 at 01:19
  • 1
    Hi tcaditot0, I have just changed my question to be simpler. table B does not have region_id of 2 and 3. Therefore, by default, table A wants to look up the value in table B if the region_id is match, however, it table A can't find it, it will look up the value of region_id 0 – hnandarusdy Apr 26 '19 at 01:28
  • I was a bit confused as well @hnandarusdy . I have this habit of not grasping the true meaning of others' question ;) – FanoFN Apr 26 '19 at 02:07

4 Answers4

2

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
Michael Z.
  • 1,453
  • 1
  • 15
  • 21
  • I had a quite similar approach only with slight differences but I have test this query and it works! – FanoFN Apr 26 '19 at 02:04
  • Thanks Michael. I ticked this as an answer, this seems to work, however, I find my alternatives because I do not want to join to many times as the table consists of many records (>5000000). I use IFNULL method. – hnandarusdy Apr 29 '19 at 23:16
  • @hnandarusdy thank you, I appreciate that and I do see your point. It's certainly worth your effort to test each one for the most optimal execution. My reasoning behind this implementation was to keep it aligned with what you already had. Absolutely you should implement the best way for your situation. – Michael Z. Apr 29 '19 at 23:24
  • No probs and thanks for your help Michael. It gave me the idea though :-) – hnandarusdy Apr 30 '19 at 00:23
2

Check if the following SQL works for you:

SELECT a.id
,      a.regionID
,      b.rate
FROM table_a a 
LEFT JOIN table_b b ON a.regionID = b.regionID 
    OR (b.regionID = 0 AND not EXISTS (select * from table_b b1 where b1.regionID = a.regionID))
order by a.id

Note: the condition after the OR clause says when a.regionID does not exist in table_b then use b.regionID = 0.

jxc
  • 13,553
  • 4
  • 16
  • 34
1

This is almost similar to Michael Z. solution:

SELECT r.ID,r.Region_ID,b.Rate FROM
(SELECT a.ID,a.Region_ID,ifnull(b.region_id,0) AS bRegID 
FROM table_a a 
LEFT JOIN table_b b 
on a.region_id=b.region_id) r
LEFT JOIN table_b b ON r.bregid=b.region_ID
Order by r.Region_ID;

Here a fiddle: Fiddle

FanoFN
  • 6,815
  • 2
  • 13
  • 33
  • 1
    I like yours because you do the null conversion in the select. I was going to update mine for that, but it still works so I'm leaving it. The only thing I dislike about yours is the indentation. You have none which makes it harder to see the nested query. – Michael Z. Apr 26 '19 at 02:13
  • Thanks @MichaelZ. , I'll take note of that! – FanoFN Apr 26 '19 at 02:39
0

You seem to want:

select a.companyID, a.regionID, a.status, b.rate
from table_a a left join
     table_b b
     on a.company_id = b.company_id;

region does not seem necessary for your join.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your response, let's just assuming that I need to use "region" for my join. The tables are more complicated actually. I will reiterate my question soon. – hnandarusdy Apr 26 '19 at 01:19