MariaDB concatenate 2 tables with same number of rows-CodePudding

Say I have 2 tables with exactly SAME number of rows, but no other obvious relations:

tableA

ID	items
1	banana
2	orange

tableB

itemID	volume
5550	50
5551	70

Can I join these 2 tables horizontally, to form 1 table like the following?

ID	items	itemID	volume
1	banana	5550	50
2	orange	5551	70

CodePudding user response：

If you have 2 tables with exactly SAME number of rows, but no other obvious relations and on both tables , respectively ID and itemID defines the uniqueness of the rows you can apply MySQL ROW_NUMBER Function and join on the row_number, the order by clause is important.

Try:

SELECT tbla.ID, tbla.Items, tblb.ItemId, tblb.volume
FROM (
       SELECT ID, Items, row_number() over( order by ID desc )row_numA
       FROM TableA
     )tbla      
INNER join
     (  SELECT ItemId,volume,row_number() over(order by ItemId desc)row_numB
        FROM TableB
     ) tblb ON tbla.row_numA=tblb.row_numB
order by tbla.ID asc;

https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=15d13d29a84a55c4d029115c87eebe8f

CodePudding user response：

try this

create table TableA(ID INT, Items varchar(20));
create table TableB(ItemId INT, volume varchar(20));

insert into TableA(Id, items) values (1, 'banana'), (2, 'orange');
insert into TableB(ItemId, volume) values (5550, '50'), (5551, '70');

SELECT A.ID, A.Items, B.ItemId, B.volume
FROM
(
   SELECT ID, Items, rownum()R
   FROM TableA
)A INNER join
(
  SELECT ItemId,volume,rownum()R
  FROM TableB
)B ON A.R=B.R