Create view in mysql from multiple tables

Databases
1

does anybody know what the correct syntax is?

I need to do the following:

create view myView as select * from tOne, tTwo, tThree;

this does not work in mysql. How this can be done?

thanks

2

first you have to have mysql 5.0 or newer to do views.

second, i don’t think you wrote that view correctly. are you sure you want a cartesian product between three tables? are you sure you don’t want a union?

create view
       myView as
select *
  from tOne
 union
select *
  from tTwo
 union
select *
  from tThree
3

no wonder it was not working - I have mysql 4.1, so there is no way to create views in 4.1?

Actually I do not need a view at all :slight_smile:

When I do this select * from tableone, tableTwo, tableThree …
it selects everything and adds tables horizontally.
How can I append all of them one AFTER another vertically?
All tables have the same definition.

Thanks.

4

see post #2 in this thread and remove create view myView as

5

thanks,

however I do not need a union. I want to keep all rows even though they are uqual

6

That is what union does. If you want dupes, you can use UNION ALL.

7

thanks,

there is another problem that I ma facing right now:

My query:


SELECT *
FROM ra UNION SELECT *
FROM rr UNION SELECT *
FROM rh UNION SELECT *
FROM ro
WHERE
	 index < '20'
ORDER BY id DESC;

this does not select rows from 4 tables where index < 20 instead it selects everything from all four tables.

How can I run a query that selects from all four tables only those rows where index is less that 20?

thanks

8

SELECT * FROM ra WHERE index < ‘20’ UNION ALL
SELECT * FROM rr WHERE index < ‘20’ UNION ALL
SELECT * FROM rh WHERE index < ‘20’ UNION ALL
SELECT * FROM ro WHERE index < ‘20’
ORDER BY id DESC;

9