mysql - MySQL multi-table join with nulls

Question

I have the following two tables:

Codes
Code  key1   key2
1     a      hh
2     a   
3     a   
4     a   


Orders
key1   key2  val1
a      aa    foo
h      hh    bar

And I'd like to get

Desired output
code   key1   key2  val1
1      a      hh    bar 
2      a
3      a
4      a

I've tried it with the inner join on orders:

select 
      ordersa.key2, 
      code, 
      ordersb.date
   from 
      order ordersa
         right join codes
            on codes.key1 = orders.key1
         inner join orders ordersb
            on codes.key2 = ordersb.key2

But that takes out all the null data in key2 column and I get:

code   key1   key2   val1
1      a      hh     bar 

Any suggestions on how I can achieve that?

标签： mysql

Answer 1

SELECT
  c.Code,
  c.key1,
  IFNULL(c.key2, '') key2,
  IFNULL(o.val1, '') val1,
FROM Codes c
LEFT JOIN Orders o ON c.key2 = o.key2