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
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 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

Open in new window

Who is Participating?
TextReportConnect With a Mentor Commented:
You will probably find cursors slower that joining the tables. How long do the queries take and how long if you join them?

I don't have examples of the cursor approach but the logic would be

Cursor 1
Cursor 2
Fetch from 1 - @price and @volume
Fetch from 2 - @lotsize and @time
INSERT INTO table3 (price, volume, lotSize, time) VALUES (@price, @volume, @lotsize, @time)
Fetch Next From 1
Fetch Next From 2

Cheers, Andrew
SweatCoderConnect With a Mentor Commented:
You could dump the results of both into a 3rd table, perhaps a temp table, and the structure of the 3rd table could be designed to have the select/columns just how you like.
jmokrauerAuthor Commented:

I know how to do that via

'Insert into select...'

using only one select statement, but how would it do it with two...
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
jmokrauerAuthor Commented:

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?
All Courses

From novice to tech pro — start learning today.