CODEWITHSUNDEEP
mysqlbash
jm_
jm_

Reputation: 641

Getting the output of several MySQL queries all together

I am writing a script thats gets some data from different mysql tables:

#!/bin/bash
/usr/bin/mysql --password=****** --database=mydb<<EOMYSQL
select field1, field2 from table1 left join table2 on etc
into outfile '/home/jm/mysql/file1.txt';
select field3 from table3 left join table4 on etc
into outfile '/home/jm/mysql/file2.txt';
select field4,field5,field6,field7 from table5 left join table6 on etc
into outfile '/home/jm/mysql/file3.txt';
EOMYSQL

My "problem" is that I need to use 3 differents output files, and merge them afterwards. Is there any way to get the output of the 3 queries all together?, like:

field1 field2 field3 field4 field5 field6 field7

Thanks,

PS: tried "union all" but looks like requieres same number of fields per query.

Upvotes: 1

Views: 162

Answers (4)

AndreKR
AndreKR

Reputation: 33678

Yes, UNION requires the same number of fields for each query, but you can fill them for example with an empty string:

select field1, field2, "", "", "", "", "" from table1 left join table2 on etc
union
select "", "", field3, "", "", "", "" from table3 left join table4 on etc
union
select "", "", "", field4, field5, field6, field7 from table5 left join table6 on etc

You can also use NULL:

select field1, field2, NULL, NULL, NULL, NULL, NULL from table1 left join table2 on etc
union
select NULL, NULL, field3, NULL, NULL, NULL, NULL from table3 left join table4 on etc
union
select NULL, NULL, NULL, field4, field5, field6, field7 from table5 left join table6 on etc

Upvotes: 3

Taryn
Taryn

Reputation: 247720

Without seeing any sample data or even your table structures, if you want to perform this in SQL then you might be able to use the following.

If you have a field that can be joined on between each of the query results, then you could use a LEFT JOIN to return data:

select *
from
(
  select t1.field1, t1.field2
  from table1 t1
  left join table2 t2
    on t1.field1 = t2.field1
) t1t2
left join
(
  select t3.field3
  from table3 t3
  left join table4 t4
    on t3.field3 = t4.field3
) t3t4
  on t1t2.field1 = t3t4.field3
left join
(
  select t5.field4, t5.field5, t5.field6, t5.field7
  from table5 t5
  left join table6 t6
    on t5.field4 = t6.field4
) t5t6
  on t1t2.field1 = t5t6.field4;

See SQL Fiddle with Demo

If you do not have a field that could be joined on, then you could use a CROSS JOIN:

select *
from
(
  select t1.field1, t1.field2
  from table1 t1
  left join table2 t2
    on t1.field1 = t2.field1
) t1t2
cross join
(
  select t3.field3
  from table3 t3
  left join table4 t4
    on t3.field3 = t4.field3
) t3t4
cross join
(
  select t5.field4, t5.field5, t5.field6, t5.field7
  from table5 t5
  left join table6 t6
    on t5.field4 = t6.field4
) t5t6;

See SQL Fiddle with Demo

Upvotes: 1

eduffy
eduffy

Reputation: 40224

It looks like you need the paste command.

% cat A.csv 
dog,cat
bird,tiger

% cat B.csv 
bear,lion
weasel,trout

% paste -d, A.csv B.csv 
dog,cat,bear,lion
bird,tiger,weasel,trout

The -d flag changes the delimiter (the default is TAB).

Upvotes: 1

Cynical
Cynical

Reputation: 9578

If you want to join those files by column, you should use the paste utility:

cd /home/jm/mysql/
paste file1.txt file2.txt file3.txt

Upvotes: 1

Related Questions