Employee details with their Job Type

Question

I am having a DB with following tables :

 create table EMPLOYEE
 (
   Emp_ID INT NOT NULL AUTO_INCREMENT,
   Emp_FName VARCHAR(30) NOT NULL,
   Emp_LName VARCHAR(30) NOT NULL,
   Address_ID_Resident INT REFERENCES ADDRESS(Address_ID),
   JobTime_ID CHAR(1) REFERENCES JOBTIME(JobTime_ID),
   PRIMARY KEY(Emp_ID)
 );

Other Table ADDRESS is as follow :

 create table ADDRESS
 (
    Address_ID INT NOT NULL AUTO_INCREMENT,
    Address_St VARCHAR(50) NOT NULL,
    Address_City VARCHAR(30) NOT NULL,
    Address_State VARCHAR(3) NOT NULL,
    Address_PostCode CHAR(4) NOT NULL,
    Add_TypeID CHAR(1) REFERENCES ADDRESSTYPE(Add_TypeID),
    PRIMARY KEY(Address_ID)
 );

ADDRESSTYPE Table is to tell whether address is Residential, Office, Postal

create table ADDRESSTYPE
(
  AddType_ID CHAR(1) NOT NULL,
  Add_Type VARCHAR(15) NOT NULL,
  PRIMARY KEY(AddType_ID)
);

JobTime Table describes whether job of employee is Full Time or Casual.

Create table JOBTIME
(
  JobTime_ID CHAR(1) NOT NULL,
  JobTime_Desc VARCHAR(10) NOT NULL,
  PRIMARY KEY(JobTime_ID)
);

Now as each of them will be having a different salary so For fulltime and casual we have two seperate tables.

 Create table FULLTIME
 (
   Emp_ID INT NOT NULL,
   Emp_salary_yearly DOUBLE(10,2) NOT NULL,
   JobType_ID INT REFERENCES JOBTYPE(JobType_ID),
   FullTimeJob_ID CHAR(1) NOT NULL DEFAULT 'F',
   PRIMARY KEY(Emp_ID)
  );

  ALTER TABLE FULLTIME ADD FOREIGN KEY(Emp_ID) REFERENCES EMPLOYEE(Emp_ID);

  ALTER TABLE FULLTIME ADD FOREIGN KEY(FullTimeJob_ID) REFERENCES JOBFULLTIME(FullTimeJob_ID);

 Create table CASUALTIME
 (
   Emp_ID INT NOT NULL,
   Emp_salary_hourly DOUBLE(10,2) NOT NULL,
   JobType_ID INT REFERENCES JOBTYPE(JobType_ID),
   CasualJob_ID CHAR(1) NOT NULL DEFAULT 'C',
   PRIMARY KEY(EMP_ID)
 );

 ALTER TABLE CASUALTIME ADD FOREIGN KEY(Emp_ID) REFERENCES EMPLOYEE(Emp_ID);

 ALTER TABLE CASUALTIME ADD FOREIGN KEY(CasualJob_ID) REFERENCES JOBCASUALTIME(CasualJob_ID);

Now I want list of names , complete address of all employees sorted by their salary and indicate if the employee works fulltime or casual. Here name is combination of both FName and LName represented in Name column and combination of Street, Suburb State Postcode (e.g. 123 Anzac Pde, Maroubra NSW 2038) in a column labelled ADDRESS . Now I know to do it with help of Join Statements. But if we are supposed to do it without use of JOIN statement then what can be SQL Query for this problem ?

I need to take Address from ADDRESS TABLE and Salary from FULLTIME and CASUAL table depending on job type of employee

Answer 1

You have table inheritance - CASUALTIME and FULLTIME inherit off EMPLOYEE. Assuming that an employee MUST be either casual or fulltime, and can't be BOTH casual and fulltime simultaneously, what you can do is use a union to combine the casual and fulltime employees in a derived table (I've called this x), and obtaining a single 'salary' field before joining to the rest of the tables (you will need joins):

 SELECT e.Emp_FName, e.Emp_LName, a.Address_St, a.Address_City, 
        a.Address_State, a.Address_PostCode, x.Salary
 FROM 
 (
    SELECT ct.emp_id, ct.Emp_salary_hourly AS Salary
    FROM CASUALTIME ct

    UNION

    SELECT ft.emp_id, ft.Emp_salary_yearly AS Salary
    FROM FULLTIME ft
 ) x
 INNER JOIN  EMPLOYEE e on x.Emp_ID = e.Emp_ID
 INNER JOIN ADDRESS a on e.Address_ID_Resident = a.Address_ID
 -- Can join to JobType and AddressType same way

It does seem a bit strange to list an annual salary and an hourly rate in a single column as these have different units - it would make more sense to convert the hourly rate to an annual salary or vice versa so they have the same unit.

Also, you shouldn't be using DOUBLE to store financial data - rather use DECIMAL

I've also assumed that JOBTIME is a typo - the table is surely JobType ?

Answer 2

Keeping 2 tables that are almost identical for Full Time and Casual seems to me like the wrong thing to do.
I would merge them to a single table called Salary, and rename the Emp_salary_yearly and Emp_salary_hourly to Emp_salary, since you already have the column for JobType_ID in both tables. Also, the CasualJob_ID and FullTimeJob_ID columns are redundant and useless.

Keep in mind that a salary and job type for any employee may change, so it would be a good idea to keep FromDate and ToDate columns in the salary table as well (using NULL in the ToDate column to indicate that the records is still valid on the current date).

So My suggestion goes like this:

Create table Salary
(
    Emp_ID INT NOT NULL REFERENCES EMPLOYEE(Emp_ID),
    Emp_salary DOUBLE(10,2) NOT NULL,
    JobType_ID INT REFERENCES JOBTYPE(JobType_ID),
    FromDate date NOT NULL,
    ToDate date NULL,
    PRIMARY KEY(Emp_ID, FromDate)
);

Note: this table's primary key is both the employee id and the from date.

This will make for a simple sql statement that looks like this:

SELECT CONCAT(Emp_FName, ' ', Emp_LName) As FullName, 
       CONCAT(Address_St, ' ', Address_City, ' ', Address_PostCode) As Address,
       Emp_salary_yearly,

       JobType_Desc,
       FromDate,
       ToDate
FROM EMPLOYEE e 
INNER JOIN ADDRESS a ON e.Address_ID_Resident = a.Address_ID
INNER JOIN Salary s ON(e.Emp_Id = Salary.Emp_Id)
INNER JOIN JOBTYPE j ON(s.JobType_ID = j.JobType_ID)

Update Answering your comments: First, don't use implicit joins. ANSI-SQL supports explicit joins for more then 20 years now, and there really is nothing better in implicit joins.
Explicit joins are more readable and easier to handle then implicit joins.

Second, in the case where the 2 salary tables can't be combined into one, you can either use a LEFT JOIN on both, or use an INNER JOIN on a derived table that is the result of a UNION between them as suggested by StuartLC in his answer.

This is how it would look like using left joins:

SELECT CONCAT(Emp_FName, ' ', Emp_LName) As FullName, 
       CONCAT(Address_St, ' ', Address_City, ' ', Address_PostCode) As Address,
       CASE WHEN Emp_salary_hourly IS NOT NULL THEN  
               CONCAT('$', LPAD(Emp_salary_hourly, 7, ' ') 
            WHEN Emp_salary_yearly IS NOT NULL THEN  
               CONCAT('$', LPAD(Emp_salary_yearly , 7, ' ')
       END As Emp_Salary, 
       JobType_Desc,
       FromDate,
       ToDate
FROM EMPLOYEE e 
INNER JOIN ADDRESS a ON e.Address_ID_Resident = a.Address_ID
INNER JOIN JOBTIME j ON(e.JobTime_ID = j.JobTime_ID )
LEFT JOIN FULLTIME f ON(e.Emp_Id = f.Emp_Id)
LEFT JOIN CASUALTIME c ON(e.Emp_Id = c.Emp_Id)

Note #1: This assumes that the employee can only have one job time.
Note #2: If neither the FULLTIME nor the CASUALTIME have a record for the emp_id then Emp_Salary will be null.