CODEWITHSUNDEEP
mysqlview
frcamacho
frcamacho

Reputation: 180

Combing data from multiple tables using view on mysql

I have two tables Employee and Customer.

I want an Employee's Name and Address and then a Customer's Name and Address in one view.

This is what I have: 

CREATE VIEW Mail_List AS
SELECT C.CustName, C.CustAddress
FROM Customers C
UNION
Select E.EmpCustName, E.EmpCustAddress
From Employees E;

But it says No rows were affected. Please help!

Upvotes: 0

Views: 41

Answers (1)

Ross Smith II
Ross Smith II

Reputation: 12179

No rows were affected is not an error that should appear for SELECT operations.

How are you calling the view? Try doing this:

SELECT * FROM Mail_List;

For clarity, you might rewrite the view as:

CREATE OR REPLACE VIEW Mail_List AS
SELECT C.CustName AS name, C.CustAddress AS address, 'customer' AS `type`
FROM Customers C
UNION ALL
SELECT E.EmpCustName AS name, E.EmpCustAddress AS address, 'employee' AS `type`
From Employees E
ORDER BY name;

Upvotes: 1

Related Questions