Cfrgtkky

I have created the following tables:

CREATE TABLE Employees 

(

    EmployeeID      int         IDENTITY(1,1) PRIMARY KEY

    ,EmployeeNumber int         UNIQUE

    ,DateOfBirth    datetime        NOT NULL

    ,FirstName      nvarchar(14)    NOT NULL

    ,MiddleName     nvarchar(14)    NOT NULL

    ,LastName       nvarchar(16)    NOT NULL

    ,DateHired      datetime        NOT NULL 

)



CREATE TABLE Customers 

(

    CustomerID      int         IDENTITY(1,1) PRIMARY KEY

    ,FirstName      nvarchar(14)    NOT NULL

    ,MiddleName     nvarchar(14)    NOT NULL

    ,LastName       nvarchar(16)    NOT NULL

    ,DateLastVisited datetime       NOT NULL 

    ,EmailAddress   nvarchar(52)    NOT NULL

)



CREATE TABLE Departments 

(

    DepartmentID    int         IDENTITY(1,1) PRIMARY KEY

    ,Code           nchar(4)        UNIQUE

    ,Name           nvarchar(40)    NOT NULL 

)



CREATE TABLE DepartmentEmployees 

(

    DepartmentEmployeeID    int     IDENTITY(1,1) PRIMARY KEY

    ,EmployeeID     int             NOT NULL

            CONSTRAINT Department_Employee REFERENCES Employees(EmployeeID)

    ,DepartmentID   int         NOT NULL

            CONSTRAINT Employee_Department REFERENCES Departments(DepartmentID)

    ,DateStarted    datetime        NOT NULL

    ,DateEnded      datetime        NOT NULL 

)



CREATE TABLE Salaries 

(

    SalaryID        int         IDENTITY(1,1) PRIMARY KEY

    ,EmployeeID     int         NOT NULL

            CONSTRAINT Salaried_Employee REFERENCES Employees(EmployeeID)

    ,Amount     money           NOT NULL

    ,DateStarts     datetime        NOT NULL

    ,DateEnds       datetime        NOT NULL 

)

I have inserted a data according to the table requirement.

I am working on this question: Get all employees first and last name, with a department code and a salary

I have written this query for the question but it is not working.

SELECT  

    E.FirstName, E.LastName, D.Code, S.Amount 

FROM

    Employees E

INNER JOIN

    Salaries S ON E.EmployeeID = S.EmployeeID

INNER JOIN

    Departments D ON D.DepartmentID = E.EmployeeID

Please help me out

SELECT  E.FirstName,E.LastName, D.Code,S.Amount From Employees E

Inner Join Salaries S

On E.EmployeeID= S.EmployeeID

Inner join Departments D

**on D.DepartmentID= E.EmployeeID**

Are you sure you want to join with departmentID to EmployeeID?

This query will give you all you need

select  firstname, lastname ,Departments.Code as DepartmentCode,Salaries.Amount  as Salary

from 

Employees 

inner join DepartmentEmployees on Employees.EmployeeID = DepartmentEmployees.EmployeeID  

inner join Departments on   DepartmentEmployees.DepartmentID   = Departments.DepartmentID  

inner join Salaries on Employees.EmployeeID = Salaries.EmployeeID 

Every Employee belongs to a department( DepartmentEmployees). Because of that you must join Employee with DepartmentEmployees. In next join you can find the code of Department and in 3rd join you find the salary amount from Salaries table.