MySQL: export multiple tables to CSV file

Question

I'm trying to export multiple MySQL tables into a single CSV file, these tables have different number of columns and have nothing in common. An example is below:

table1:

ID|    Name
1 |    Ted
2 |    Marry
null|    null

table2:

Married|    Age    |    Phone
Y      |    35     |    No
N      |    25     |    Yes
Y      |    45     |    No

The result that I want to get is:

ID|    Name  |   Married    |    Age    |     Phone
1 |    Ted   |   Y          |    35     |     No
2 |    Marry |   N          |    25     |     Yes
null|  null  |   Y          |    45     |     No

Is it possible to do using MySQL commands? I have tried all types of join but it doesn't give me the result that I need.

In that case how do you know Marry is 25 and has a phone? In other words how are you supposed to match table1 and table2? — vhu
– vhu, Commented Jul 17, 2015 at 8:48
What's the relation between the 2 tables? Is it the row number (if so, its a very bad idea!) — Alepac
– Alepac, Commented Jul 17, 2015 at 8:50
Does it matter if all rows have 25 as age and Marry as name? — vhu
– vhu, Commented Jul 17, 2015 at 8:54

Alepac · Accepted Answer · 2015-07-17 09:35:39Z

1

Try this query:

SELECT * FROM 
   (SELECT @n1:=@n1+1 as 'index', Table1.*  FROM Table1, (select @n1:=0)t)t1 
        natural join (SELECT @n2:=@n2+1 as 'index', Table2.*  FROM Table2, (select @n2:=0)t1)t2 ;

You can see in this fiddle a working example.

In the example we generate an index column for Table1 and Table2 and natural join the 2 tables.

This way the join is done using the row position as returned by the SELECT of tables without any ORDER operator and usually this is not a good idea!

edited Jul 17, 2015 at 9:35

answered Jul 17, 2015 at 9:07

Alepac

1,84313 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PaulF Over a year ago

You should be aware that, while usually in natural order, the order of data return by a select (without an order by) is not guaranteed. see dba.stackexchange.com/questions/5774/…. If there are gaps in the ID in Table1 then there will be a problem too.

Trung Bún Over a year ago

if there is a gap in table 1, a null value would be expected to be there, I would add this to my question to make this clear

Alepac Over a year ago

What do you mean with a gap? a row with a null ID or row with ID = 4 and no row with ID =3 ?

Alepac Over a year ago

@Trung I edited the solution to generate index column for both tables

vhu · Accepted Answer · 2015-07-17 09:07:36Z

0

I'm not quite sure you understand what you are asking but sure you can join two tables without really caring on which rows will be matched:

SET @i1=0;
SET @i2=0;

SELECT * INTO OUTFILE 'xyz' FIELDS TERMINATED BY ',' 
 FROM (SELECT @i1:=@i1+1 as i1, table1.* FROM table1) AS a
 JOIN (SELECT @i2:=@i2+1 as i2, table2.* FROM table2) b ON (a.i1=b.i2);

answered Jul 17, 2015 at 9:07

vhu

12.9k11 gold badges42 silver badges50 bronze badges

3 Comments

PaulF Over a year ago

See comment above - this is not guaranteed to give consistent results.

vhu Over a year ago

@PaulF: that's the point. OP doesn't care how the rows are matches, hence the first sentence in the answer.

PaulF Over a year ago

As far as I understood OP he doesn't care "how" the join is done but the tables are linked in natural row order. I may be wrong on that - OP could advise.

Collectives™ on Stack Overflow

MySQL: export multiple tables to CSV file

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related