Creating a Mysql view to SELECT coloumns from different tables

Question

I need help in constructing a VIEW on 4 tables. The view should contain the following columns:

ER.ID, ER.EMPID, ER.CUSTID, ER.STATUS, ER.DATEREPORTED, ER.REPORT, EB.NAME, CR.CUSTNAME, CR.LOCID, CL.LOCNAME, DI.DEPTNAME

The aliases are:

EMP_REPORT ER, EMP_BIO EB, CUST_RECORD CR, CUST_LOC CL, DEPT_ID DI

The data models are:

describe EMP_REPORT;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment | 
| empid        | int(11)     | NO   |     | NULL    |                | 
| custid       | int(11)     | NO   |     | NULL    |                | 
| status       | varchar(32) | NO   |     | NULL    |                | 
| datereported | bigint(20)  | NO   |     | NULL    |                | 
| report       | text        | YES  |     | NULL    |                | 
+--------------+-------------+------+-----+---------+----------------+

describe EMP_BIO;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| empid  | int(11)     | NO   | PRI | NULL    |       | 
| name   | varchar(56) | NO   |     | NULL    |       | 
| sex    | char(1)     | NO   |     | NULL    |       | 
| deptid | int(11)     | NO   |     | NULL    |       | 
| email  | varchar(32) | NO   |     | NULL    |       | 
| mobile | bigint(20)  | YES  |     | NULL    |       | 
| gtlk   | varchar(32) | YES  |     | NULL    |       | 
| skype  | varchar(32) | YES  |     | NULL    |       | 
| cvid   | int(11)     | YES  |     | NULL    |       | 
+--------+-------------+------+-----+---------+-------+

 describe CUST_RECORD;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| custid   | int(11)      | NO   | PRI | NULL    | auto_increment | 
| custname | varchar(32)  | NO   |     | NULL    |                | 
| address  | varchar(255) | YES  |     | NULL    |                | 
| contactp | varchar(32)  | YES  |     | NULL    |                | 
| mobile   | bigint(20)   | YES  |     | NULL    |                | 
| locid    | int(11)      | NO   |     | NULL    |                | 
| remarks  | text         | YES  |     | NULL    |                | 
| date     | int(11)      | YES  |     | NULL    |                | 
| addedby  | int(11)      | YES  |     | NULL    |                | 
+----------+--------------+------+-----+---------+----------------+

describe CUST_LOC;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| locid   | int(11)     | NO   | PRI | 0       |       | 
| locname | varchar(32) | NO   |     | NULL    |       | 
+---------+-------------+------+-----+---------+-------+

describe DEPT_ID;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptid   | int(11)     | NO   |     | NULL    |       | 
| deptname | varchar(32) | YES  |     | NULL    |       | 
+----------+-------------+------+-----+---------+-------+

The table EMP_REPORT contains reports submitted by employees, all the coloumns in it needs to be fetched. The empid in this table should be used to fetch corresponding name in EMP_BIO (employee biodata) table. The custid in EMP_REPORT should be used to fetch corresponding locid in CUST_RECORD(customer record) which is used to fetch locname in CUST_LOC(customer location) table. The empid in EMP_REPORT is used to fetch corresponding deptid in EMP_BIO table which is then used to fetch corresponding deptname from DEPT_ID(department id) table.

I tried constructing view using union of different select queries, but didn't get the correct results. Please help me.

Answer 1

Don't use unions, use joins:

SELECT ER.ID, ER.EMPID, ER.CUSTID, ER.STATUS, ER.DATEREPORTED, ER.REPORT, EB.NAME, CR.CUSTNAME, CR.LOCID, CL.LOCNAME, DI.DEPTNAME 
FROM EMP_REPORT er 
JOIN EMP_BIO eb ON eb.empid = er.empid
JOIN CUST_RECORD cr ON cr.custid = er.custid
JOIN CUST_LOC cl ON cl.locid = cr.locid
JOIN DEPT_ID di ON di.deptid = eb.deptid

Answer 2

Create your view, then use something similar to the following (this is not exact code, you will need to substitute in the correct table names, etc)

SELECT ER.ID, ER.EMPID, ER.CUSTID, ER.STATUS, ER.DATEREPORTED, ER.REPORT, EB.NAME, CR.CUSTNAME, CR.LOCID, CL.LOCNAME, DI.DEPTNAME FROM ER JOIN EB on er.whatever=eb.whatever JOIN CR on cr.whatever=eb.whatever JOIN DL on cr.whatever=dl.whatever JOIN DI on dl.whatever=di.whatever

Whatever is the field that you are joining on.