Creating a view to display records from more than two tables

I need to create a view to display all the records from each of the employee dependent tables, and also it should show the join date of every employee as well from that particular employee's table. I used the following code, but it shows an error when I try to run it.

CREATE VIEW EmpDependent
AS
SELECT JoinDate
FROM Lecturer,BranchManager,NonAcademicStaff开发者_运维问答
INNER JOIN LecDependent.EmpRegNo=Lecturer.EmpRegNo=BranchManager.EmpRegNo=NonAcademicStaff.EmpRegNo

Please help

Your join syntax is incorrect. Generally, to join two tables A and B you would have to do the following:

select a.*, b.* 
from A a inner join B b on b.a_id = a.id

Given that table A has a column called id and table B has a column named a_id (possibly and preferably being a foreign key).

You can add as many inner joins as like, for example you could extend the above with:

inner join C on c.b_id = b.id

Once you create a select statement that returns the rows you expect, you can create a view based on that statement.

You need to use something like:

CREATE VIEW EmpDependent AS
    SELECT N.JoinDate
      FROM Lecturer         AS L
      JOIN BranchManager    AS B ON L.EmpRegNo = B.EmpRegNo
      JOIN NonAcademicStaff AS N ON B.EmpRegNo = N.EmpRegNo

However, your query lists a table LecDependent that is not cited in the FROM clause, and it is not clear why a lecturer is also a non-academic staff member and a branch manager (and maybe a lecturer is also their own dependent).

You also didn't give any indication of which table contains the JoinDate column - I guessed NonAcademicStaff, but it could be any of them. And you'd be unlikely to need a 3 table join (or a 4 table join) for a single column without a number of filter conditions.

Overall, you need to think hard about the query you are encapsulating in the view.