Information about employees indicating their type of work
I have a DB with the 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)
);
Another ADDRESS table looks like this:
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 - indicate whether the address is "Residential", "Office", "Postal"
create table ADDRESSTYPE
(
AddType_ID CHAR(1) NOT NULL,
Add_Type VARCHAR(15) NOT NULL,
PRIMARY KEY(AddType_ID)
);
The JobTime table describes whether an employee's job is complete or random.
Create table JOBTIME
(
JobTime_ID CHAR(1) NOT NULL,
JobTime_Desc VARCHAR(10) NOT NULL,
PRIMARY KEY(JobTime_ID)
);
Now that they each have a different salary, so for full and casual, we have two separate 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 need a list of names, the full address of all employees, sorted by their salary, and whether the employee is working full time or at random. Here, the name is a combination of both FName and LName represented in the Name column, and the combination of Street, Suburb State zip code (for example, 123 Anzac Pde, Maroubra NSW 2038) in the column labeled ADDRESS. Now I know to do this with a Join Statement. But if we have to do this without using a JOIN statement, then what could be the SQL Query for this problem?
I need to get address from ADDRESS TABLE and salary from FULLTIME and CASUAL tables depending on the employee's job type.
source to share
You have table inheritance - CASUALTIME
and FULLTIME
inherit off EMPLOYEE
. Assuming the employee MUST be random or full, and cannot be both random and full time, then you can use union to combine random and full employees in a derived table (I called this x
) and get one "salary" field before joining the rest of the tables (you need connections):
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 seems a bit odd to list the annual salary and hourly rate in the same column since they have different units - it would be wiser to convert the hourly rate to the annual salary, or vice versa, so that they have the same unit.
Also, you shouldn't use DOUBLE
to store financial data - rather useDECIMAL
I also assumed which JOBTIME
is a typo - the table is definitely JobType
?
source to share
Keeping two tables that are almost identical for Full Time
and Casual
doesn't seem right to me. I would concatenate them into one table named Salary
and rename Emp_salary_yearly
both Emp_salary_hourly
to Emp_salary
, since both tables already have a column for JobType_ID
. In addition, the columns CasualJob_ID
and FullTimeJob_ID
are redundant and useless.
Keep in mind that the size of the salary and the type for any employee may change, so it would be nice to keep the columns FromDate
and ToDate
in the salary table (using NULL
in ToDate
to indicate that recording is still valid in the current date).
So my proposal looks 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 primary key of this table is both the employee ID and the date.
This will make 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
Responding to your comments: First, don't use implicit joins. ANSI-SQL has supported explicit joins for over 20 years, and there is nothing better about implicit joins.
Explicit joins are more readable and easier to handle than implicit joins.
Second, in the case where two payroll tables cannot be merged into one, you can use a LEFT JOIN for both, or use an INNER JOIN on a derived table that results from a UNION between them, as suggested by StuartLC in his answer .
This is what 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 an employee can only have one working time.
Note # 2: If neither FULLTIME nor CASUALTIME has an entry for emp_id, then the Emp_Salary value will be null.
source to share