Union colums sql server vertically

Question

I have two tables that has the same columns numbers and names and I want to join its columns.

I have this table called inicial_dolares

And have this other table called inicial_cordobas

I am looking for this result

I've tried do this with joins but does not work. the common column is id_arqueo is a integer. and I tried this SQL union but vertically but does not work.

SELECT   IC.id_arqueo,
       IC.id_detalle,
       IC.descripcion,
       IC.denominacion,
       IC.cantidad,
       IC.total,
       ID.id_arqueo,
       ID.id_detalle,
       ID.descripcion,
       ID.denominacion,
       ID.cantidad,
       ID.total 


FROM dbo.inicial_cordobas IC 
 LEFT JOIN  dbo.inicial_dolares ID 
 ON ID.id_arqueo = IC.id_arqueo 

--this query returns to me 168 rows because the join looks for coincidence and 
--one table has 14 and the other has 12 rows.

have you tried using `row_number() over (order by id_detalle)`? — Ed Bangga, Sep 30 '19 at 05:38

score 3 · Accepted Answer · answered Sep 30 '19 at 05:41

you need row_number() to join your 2nd table.

SELECT   IC.id_arqueo,
       IC.id_detalle,
       IC.descripcion,
       IC.denominacion,
       IC.cantidad,
       IC.total,
       ID.id_arqueo,
       ID.id_detalle,
       ID.descripcion,
       ID.denominacion,
       ID.cantidad,
       ID.total 
FROM 
    (select row_number() over (order by id_detalle) rn, * from dbo.inicial_cordobas) IC 
LEFT JOIN  
    (select row_number() over (order by id_detalle) rn , * from dbo.inicial_dolares) ID 
ON ID.id_arqueo = IC.id_arqueo and IC.rn = ID.rn

It works perfectly thank you very much, I would like to give you a vote but I don't have enough reputation points. Really thank you very much!!!! I had been with that for a couple of hours. thank you!!! — Scott Martinez, Sep 30 '19 at 06:03

score 1 · Answer 2 · answered Sep 30 '19 at 05:46

The id_detalle columns also have a relationship that can be expressed mathematically and used:

SELECT   IC.id_arqueo,
       IC.id_detalle,
       IC.descripcion,
       IC.denominacion,
       IC.cantidad,
       IC.total,
       ID.id_arqueo,
       ID.id_detalle,
       ID.descripcion,
       ID.denominacion,
       ID.cantidad,
       ID.total 


FROM dbo.inicial_cordobas IC 
 LEFT JOIN  dbo.inicial_dolares ID 
 ON ID.id_arqueo = IC.id_arqueo 

AND ic.id_detalle + 14 = id.id_detalle

The id_detalle in ic is always 14 less than the id_detalle in id so we can make a joint condition where we add 14 to the value in ic and then relate it to the value in id

This is possibly an important learning point that joins don't have to always take the form tableA.columnA = tableB.columnB. Anything that can be expressed as a truth may work as a join condition, including formulas and maths operations on either side of the =

Union colums sql server vertically

2 Answers2