Creating a view from multiple tables Options

[tudsy]

Hi

I have 4 tables, CustomersArt,Customers,subscribers and emailcomm.

My question is, how do i create a view from these 4 tables with the column email ?

I have done a view from one table (see below):

$query = "CREATE VIEW ecovib2d AS SELECT email FROM CustomersArt";


Any help will be appreciated.

[CharlesEF]

I don't see anything wrong with the syntax. As I said before, the 4 tables share some column names. Maybe you need to specify column names instead of using the * character and use AS when column names are duplicated. Have you tested using only 1 joined table? Test with 2 tables that don't share any column names. If it works then add another table join to the test. If you start getting errors when column names are duplicated then you know you can't use the * character.

And, I'm not sure 'FULL JOIN' is valid. I don't see it in the MySQL documentation. But, maybe I just missed it.

[tudsy]

I don't see anything wrong with the syntax. As I said before, the 4 tables share some column names. Maybe you need to specify column names instead of using the * character and use AS when column names are duplicated. Have you tested using only 1 joined table? Test with 2 tables that don't share any column names. If it works then add another table join to the test. If you start getting errors when column names are duplicated then you know you can't use the * character.

And, I'm not sure 'FULL JOIN' is valid. I don't see it in the MySQL documentation. But, maybe I just missed it.

Hi

Here is the latest query:

$query = "CREATE VIEW ecovib2d AS
SELECT Customers.Customersitemid,Customers.email,Customers.name,Customers.Price,Customers.terms,Customers.Age,Customers.Date,Customers.Softbought,CustomersArt.CustomersArtitemid,CustomersArt.email,CustomersArt.graphic,CustomersArt.name,CustomersArt.Price,CustomersArt.Age,CustomersArt.Date
FROM Customers join CustomersArt on Customers.Customersitemid = CustomersArt.CustomersArtitemid
WHERE email ='$Email'";

but now I get this error:

Error Message: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '='ecovib2d@live.com'' at line 4

Any help will be appreciated.

Thanks.

[CharlesEF]

$query = "CREATE VIEW ecovib2d AS
SELECT Customers.Customersitemid,Customers.email,Customers.name,Customers.Price,Customers.terms,Customers.Age,Customers.Date,Customers.Softbought,CustomersArt.CustomersArtitemid,CustomersArt.email,CustomersArt.graphic,CustomersArt.name,CustomersArt.Price,CustomersArt.Age,CustomersArt.Date
FROM Customers join CustomersArt on Customers.Customersitemid = CustomersArt.CustomersArtitemid
WHERE email ='$Email'";

Based on the column names you posted earlier your query makes no sense. Your not using the same column names. Try this:

CODE

$query = "CREATE VIEW ecovib2d AS
         SELECT
         Customers.Itemid, Customers.email, Customers.name, Customers.Price, Customers.terms, Customers.Age, Customers.Date, Customers.Softbought,
         CustomersArt.Graphic
         FROM Customers JOIN CustomersArt ON Customers.Itemid = CustomersArt.Itemid
         WHERE Customers.email ='$Email'";

Most of your problems were typos, I think. Also, I didn't include any columns with duplicate names. First see if this query works. If it does then you can try adding columns with the same name. I have already given you an example of this, check post #7. That post uses the column 'name' from 3 tables. Notice the variable name next to each one?