Creating a Mysql view to SELECT coloumns from different tables
- by user330429
I need help in constructing a VIEW on 4 tables. The view should contain coloumns:
ER.ID, ER.EMPID, ER.CUSTID, ER.STATUS, ER.DATEREPORTED, ER.REPORT, EB.NAME, CR.CUSTNAME, CR.LOCID, CL.LOCNAME, DI.DEPTNAME
ALIASES 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 dint get proper results. Please help me.
Thanks in advance.
PS: sorry for my poor english
