two tables into one?

Question

My question is simple I just want to joint two tables into one table without any PK first table is completely different they have nothing same

table1.            table2.
|в|q|              |@|John |
|ы|a|              |£|Sara |
|в|f|              |$|ciro |
|с|g|              |%|Jo.  |
|ф|s|

what I need is this

Table3
|в|q|@|John |
|ы|a|£|Sara |
|в|f|$|ciro |
|с|g|%|Jo.  |
|ф|s|-|-    |

What makes you decide for the combinations? Why join `в|q` with `@|John` and not with, say, `£|Sara`? Does any rule apply or don't you care at all which rows to combine? — Thorsten Kettner, Nov 18 '19 at 15:12
You might want to check out this [link](https://stackoverflow.com/questions/50720672/equivalent-of-r-cbind-function-in-oracle) — SoakingHummer, Nov 18 '19 at 15:14
@ThorstenKettner I uploaded them from one big excel table by parts because mysql can't import the whole table into a database so I decided to divide it by parts and import — Georgy Fidarov, Nov 18 '19 at 15:34
@ThorstenKettner the way how they are imported is already the right way so If I just attach table one to table two without any changes will be the right way — Georgy Fidarov, Nov 18 '19 at 15:38
No, you don't understand my question. Data in tables are unordered sets. You are showing `в|q` first and `ф|s` and maybe you `select *` and get the data in this order. But this is no guaranteed order. You may get a different order if you change the query slightly, e.g. join the table with another, or just run the query another day. It is necessary you specify a rule which rows to join. If the order of the import is crucial, but there is no column in the tables that show that order, you are lost already and must start from scratch (i.e. import again, this time with some sort order indicator). — Thorsten Kettner, Nov 18 '19 at 17:11

Gordon Linoff · Accepted Answer · 2019-11-18T16:33:21.707

1

This is a little complicated. You want a "vertical" list but have nothing to match the columns. You can use row_number() and union all:

select max(t1_col1), max(t1_col2), max(t2_col1), max(t2_col2)
from ((select t1.col1 as t1_col1, t1.col2 as t1_col2,
              null as t2_col1, null as t2_col2, row_number() over () as seqnum
       from table1 t1
      ) union all
      (select null, null, t2.col1, t2.col2, row_number() over () as seqnum
       from table2 t2
      ) 
     ) t
group by seqnum;

Here is a db<>fiddle.

Note that this will keep all rows in both tables, regardless of which is longer. The specific ordering of the rows in each column is not determinate. SQL tables represent unordered sets. If you want things in a particular order, you need a column that specifies the ordering.

If you want to save this in a new table, put create table as table3 before the select. If you want to insert into an existing table, use insert.

edited Nov 18 '19 at 16:33

answered Nov 18 '19 at 15:10

Gordon Linoff

1,242,037
58
646
786

I need horizontal list I have 5 columns in table1 and 6 in second. I want to combine them into one big table3 with 11 columns. Without any changes in structure just how they are already imported just to combine them – Georgy Fidarov Nov 18 '19 at 15:42
if this is impossible to do in mysql maybe its possible to add data to and existing table like to add csv file to a table with 5 columns additional 6 from another csv file without overwriting them just adding those 6 to have 11 – Georgy Fidarov Nov 18 '19 at 15:45
@GeorgyFidarov . . . That is what this does. "Vertical" means that in each set of columns, you have rows from separate tables. Those rows have no connection across the row (well, except for the made-up one involving `seqnum`. This does what you want. – Gordon Linoff Nov 18 '19 at 15:52
it works fine but how can I now save all this combined data into a table3 for example? Because it doest save me that it just shows me how are they combined together. – Georgy Fidarov Nov 18 '19 at 16:31
is it possible to use that query for more than to columns? – Georgy Fidarov Nov 18 '19 at 16:33
@GeorgyFidarov . . . You just need to list the columns you want from each table. – Gordon Linoff Nov 18 '19 at 16:34
I am trying to make it for 5x6 columns but Its not working its says this Error Code: 1222. The used SELECT statements have a different number of columns But when I change columns into 5x5 it says this Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row_number() over () as seqnum from table1 t1 ) union all (se' at line 3 – Georgy Fidarov Nov 18 '19 at 16:46
@GeorgyFidarov . . . You have to be sure that the two queries connected by the `union` list *all* columns from both tables. – Gordon Linoff Nov 18 '19 at 16:51

two tables into one?

1 Answers1