I have multiple tables with different column names like below,

customer table: c_id, customer_name, phone_number, postal_code

Employee Table: Employee_name, e_id, mobile_no,zip_code

student Table: s_id, student_name, post_code

I need to create view to combine those tables like below

User Table: userId (c_id, e_id, s_id), userName (all name columns), zip_code, contact

Sample Data

Customer Table:

|  c_id  |  Company_Name  |  Phone |
|----------------------------------|
|  1     |  Company 1     | *******|
|----------------------------------|

Employee Table:

|  e_id  |  Employee_Name | Mobile |
|----------------------------------|
|  1     |  employee 1    | *******|
|----------------------------------|

Expected View:

|  userId  |   user_Name   | contact  |
|-------------------------------------|
|  c_1     |   Company 1   |  ******* |
|-------------------------------------|
|  e_1     |   employee 1  |  ******* |
|-------------------------------------|

how to create the view please help me, Thanks in advance

CodePudding user response：

just use Union or Union All depending on duplicate values. based on data

CREATE VIEW view_name
AS
  SELECT concat('c_',c_id)         AS userId,
         company_name AS user_Name,
         phone        AS contact
  FROM   customer
  UNION
  SELECT concat('e_',e_id),
         employee_name,
         mobile
  FROM   employee   

dbfiddle

based on tables

CREATE VIEW view_name
AS
  SELECT c_id          AS userId,
         customer_name AS userName,
         postal_code   AS zip_code,
         phone_number  AS contact
  FROM   customer
  UNION
  SELECT e_id,
         employee_name,
         zip_code,
         mobile_no
  FROM   employee
  UNION
  SELECT s_id,
         student_name,
         post_code,
         NULL
  FROM   student