how to concatenate columns from two different select statements
I have two select statements that each produce multiple columns of data and multiple rows. Each select statement produces the same number of columns and rows. I would like to produce one select statement that combines both so I see the table columns concatenated in the query browser. So if the 1st select produced results with columns: price and volume and the 2nd select produced results with columns: lotSize and time, I would like to results of the query to show price, volume, lotSize and time.
I would like to avoid using a join because the results of the select statements will have many millions of rows.
Can this be done? If so conceptually, how is it done? I have attached the select statements but it is not necessary to show me the exact statement.
Server info:
MySQL 5.0.45-community-nt via TCP/IP
MySQL Client Version 5.1.11
InnoDB tables
I would like to avoid using a join because the results of the select statements will have many millions of rows.
Can this be done? If so conceptually, how is it done? I have attached the select statements but it is not necessary to show me the exact statement.
Server info:
MySQL 5.0.45-community-nt via TCP/IP
MySQL Client Version 5.1.11
InnoDB tables
SELECT #1
SELECT correlative,DATE_FORMAT(period,'%Y-%m-%d %h:%i') dateTimeWithoutSeconds,price open,max(price) high,
min(price) low, max(period) closePeriod, count(price) nTicks, NULL close, NULL upticks, NULL downticks, NULL flatticks FROM zz_tbl_ticks_6439 group by dateTimeWithoutSeconds order by period;
SELECT #2
select dateTimeWithoutSeconds, sum(uptick) totalupticks, sum(downtick) totaldownticks, sum(flattick) totalflatticks
from (SELECT
DATE_FORMAT(tbl1.period,'%Y-%m-%d %h:%i') dateTimeWithoutSeconds,if(tbl1.price-tbl2.price>0,1,0) uptick,
if(tbl1.price-tbl2.price<0,1,0) downtick,
if(tbl1.price-tbl2.price=0,1,0) flattick
FROM zz_tbl_ticks_6439 tbl1,zz_tbl_ticks_6439 tbl2 where tbl1.correlative=tbl2.correlative-1) x2 group by x2.dateTimeWithoutSeconds
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If there is no logical join then do you have ROWNUMBER() and join on that?
Alternatively you could do this with 2 cursors and update cursor 1 with the data from cursor 2
Cheers, Andrew
Alternatively you could do this with 2 cursors and update cursor 1 with the data from cursor 2
Cheers, Andrew
ASKER
TextReport,
There is a logical join. I just think it would take two long with selects producing millions of rows.
How would you do it with two cursors? Would you provide an example with some simple select statements?
There is a logical join. I just think it would take two long with selects producing millions of rows.
How would you do it with two cursors? Would you provide an example with some simple select statements?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I know how to do that via
'Insert into select...'
using only one select statement, but how would it do it with two...