CODEWITHSUNDEEP
mysqlsqldatabase
tom1234
tom1234

Reputation: 13

mysql, select two tables, then merge results

I have two tables.

table_A
  A    
  ---- 
  a    
  b    
  c    
  d    


table_B
  B    
  ---- 
  1    
  2

and want to select it like below

  A      B   
  ----  ---- 
  a      1
  b      2
  c     null
  d     null

How can I make such a select. Do I need procedure? or just need 'join syntax'? Please help me.

Upvotes: 1

Views: 89

Answers (3)

Radim Bača
Radim Bača

Reputation: 10701

If you have mysql with 8.0.2 and higher then you can use window functions

select t.a, tb.b
from
(
  select a, count(*) over (order by a) rn
  from table_a
) t
left join table_b tb on t.rn = tb.b

demo

RESULT

a   b
-------
a   1
b   2
c   null
d   null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

SQL tables represent unordered results. You can put the values side-by-side (as I will show), but without a column specifying the ordering, the order within each list is arbitrary.

select max(a) as a, max(b) as b
from ((select (@rna := @rna + 1) as rn, a, null as b
       from a cross join (select @rna := 0) params
      ) union all
      (select (@rnb := @rnb + 1) as rn, null as a, b
       from b cross join (select @rnb := 0) params
      ) 
     ) ab
group by rn;

Upvotes: 1

Connor
Connor

Reputation: 215

You can do so in a select statement using join.

An example might be:

SELECT a.value, b.value
FROM a
  left outer join b on a.id = b.id;

But this requires that you have primary / foreign keys that relate the rows in the separate tables.

Your tables will need to look something like this:

table_A   
  --id--value-- 
   123     a    
   214     b    
   567     c    
   455     d    


table_B   
  --id--value-- 
   123     1    
   214     2

Upvotes: 0

Related Questions