Problem in Creating Table

Question

Hello All,

create table mainTable as select curr_Table.empID as empID,
(currTable.ToTalDays-oldTable.ToTalDays) as DiffDays 
from currTable left outer join oldTable
on currTable.empID  = oldTable.empID

This is the query that i use to find the days worked by an employee.

The Problem raises when there is "New Joinee". "oldTable.ToTalDays" will not have any value as no record will be found for "New Joinee" in oldTable. So, for this record DiffDays (Integer-null) results is Zero instead of current total days.

Any way to resolve this problem?

Answer 1

Not perfectly sure about this one, but I don't think mysql allows

CREATE TABLE AS SELECT ...

sort of things. Doublecheck the manual on that one. Have seen such queries on postgres, but don't remember such ones on mysql...

EDIT:

Performed the double check too and have to admit that CREATE TABLE AS SELECT ... works in deed. Nevermind, gonna get some coffee...

Answer 2

create table mainTable as
select curr_Table.empID as empID,
       (currTable.ToTalDays - ifnull(oldTable.ToTalDays, 0)) as DiffDays 
  from currTable
         left outer join
       oldTable  on currTable.empID = oldTable.empID