Geek asks and answers

SQL, Android SQLITE Join

I have 2 tables that look like this:

Table A
key1 key2 key3
1    a    a
2    a    a
3    a    a

Table B
key1 key2 key3
1    xxx  zzz
2    yyy  www

Now I want to have a large table from A and B where A.key1 corresponds to B.key1, the contents of B are inserted otherwise, if no key1 matches the default values,

eg

Key1 Key2 Key3 Key5 Key6
1    a    a    xxx  zzz            
2    a    a    yyy  www               
3    a    a    0    0

What is a request?

+3
source share
3 answers
select * from a left outer join b on a.key1 = b.key1

(this will give zeros for b columns where there is no corresponding row from b)

+2
source

you need LEFT CONNECTION:

select
  a.key1,
  a.key2,
  a.key3,
  ifnull(b.key2, 0) as key4,
  ifnull(b.key3, 3) as key5
from
  tableA a
left join
  tableB b on b.key1=a.key1
+3
source 
select key1, key2, key3, 
    case key5 when null then default(key5) else key5 as key5,  
    case key6 when null then default(key6) else key6 as key6
from a
left outer join b on a.key1 = b.key1

Edit

SQLFiddle example

select a.key1, a.key2, a.key3, 
       ifnull(b.key2, 0) as key5,  
       ifnull(b.key2, 3) as key6
from a
left outer join b on a.key1 = b.key1
+1
source

More articles:


All Articles