CODEWITHSUNDEEP
mysql
burf
burf

Reputation: 25

Output the result of a mysql query, with multiple UNION, into CSV

I want to output the results of a query that uses multiple joins, group by and order by, into a CSV file.

The query itself generates the result-set, a blank row and a totals row using UNION.

When I try to output the file into a CSV using a UNION ALL, it gives the error

select "header1", "header2", "header3"
UNION ALL
(  
   select field1, field2, field3
   from tablename1
   UNION
   select "","",""
   UNION
   select "Total", field2, field3
)
into outfile 'c:\\data.csv' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';


Err 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 'UNION (select "","","","","","","","","","","" ) ' at line <6>

Thanks in advance for your help.

Upvotes: 0

Views: 446

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

Note that something like this:

select 1, 2, 3
union all
(
  select 0,4,6
  union
  select 1,1,1
)

will not work.

You should use a derived table like this instead:

select 1, 2, 3
union all
select * from (
  select 0,4,6
  union
  select 1,1,1
) x

Upvotes: 1

Related Questions