how can I join two tables of the same structure into one talbe?

[asuknow]

e.g.:
I have two tables, one is "fruit", the other is "drink"

"fruit" has the following data structure:
name price
apple 0.8
orange 1.0
grape 0.99

"drink" has the following data structure:
name price
water 1.00
milk 2.00
jiuce 1.50

I want to join these two tables into one table named "fruitdrink" like this:
name price
apple 0.8
orange 1.0
grape 0.99
water 1.00
milk 2.00
jiuce 1.50

How can I use SELECT and JOIN to CREATE a "fruitdrink"
table in Active Server Pages?
I'll appreciate your reply. Thanks !

 

[TechnicalAnalysis]

How about using UNION?
sqlstr="SELECT * FROM fruit "
sqlstr=sqlstr & "UNION SELECT * FROM drink"

Regards

http://www.technical.analysis.dingojunction.com

 

[asuknow]

I tried your method and it works.
Thank you very much for your reply.
But I am also wondering whether generating a combined
recordset from several tables will take the server longer time than generating several individual recordsets from
each table.
Does anyone has any idea of the efficiencies of different
ways of generating (combined) recordset?
Thanks again!

 

[asuknow]

There is one problem by using SELECT UNION to
join two tables of the same data structure:
When the two tables have the same records,
the recordset generated by SELECT UNION will
have two records of the same.

e.g. :
table "fruit" has the following structure:
name price quantity
apple 0.8 100
orange 1.0 200
grape 0.99 300

table "onsale" has the following structure:
name price quantity
bread 0.5 250
egg 1.2 500
orange 1.0 200

if I use:
SELECT * FROM fruit UNION SELECT * FROM onsale
(I do NOT use UNION ALL in order to remove duplicates
from the result set)
Surprsingly, the recordset generated by this way
has duplicate records of "orange"!

Can anyone tell me how come this could happen?
Thanks!