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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.