mysql - Employee details with their Job Type -

i having db 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 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 tell whether address 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 full time or casual.

create table jobtime (   jobtime_id char(1) not null,   jobtime_desc varchar(10) not null,   primary key(jobtime_id) ); 

now each of them having different salary fulltime , casual have 2 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 want list of names , complete address of employees sorted salary , indicate if employee works fulltime or casual. here name combination of both fname , lname represented in name column , combination of street, suburb state postcode (e.g. 123 anzac pde, maroubra nsw 2038) in column labelled address . know of join statements. if supposed without use of join statement can sql query problem ?

i need take address address table , salary fulltime , casual table depending on job type of employee

you have table inheritance - casualtime , fulltime inherit off employee. assuming employee must either casual or fulltime, , can't both casual , fulltime simultaneously, can use union combine casual , fulltime employees in derived table (i've called x), , obtaining single 'salary' field before joining rest of tables (you need joins):

 select e.emp_fname, e.emp_lname, a.address_st, a.address_city,          a.address_state, a.address_postcode, x.salary    (     select ct.emp_id, ct.emp_salary_hourly salary     casualtime ct      union      select ft.emp_id, ft.emp_salary_yearly salary     fulltime ft  ) x  inner join  employee e on x.emp_id = e.emp_id  inner join address on e.address_id_resident = a.address_id  -- can join jobtype , addresstype same way 

it seem bit strange list annual salary , hourly rate in single column these have different units - make more sense convert hourly rate annual salary or vice versa have same unit.

also, shouldn't using double store financial data - rather use decimal

i've assumed jobtime typo - table surely jobtype ?