Problem in Creating Table

Question

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?

Never matters as i am interested in learning and doing homeworks through out my life :) — Gopi
– Gopi, Commented Oct 13, 2009 at 7:29

KB22 · Accepted Answer · 2009-10-13 07:11:18Z

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...

edited Oct 13, 2009 at 7:11

answered Oct 13, 2009 at 6:59

KB22

7,0099 gold badges46 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gopi Over a year ago

i found this "CREATE TABLE new_tbl SELECT * FROM orig_tbl" in the above link and still i found "CREATE TABLE AS SELECT ..." works fine!

Ian Kemp - SO dead by AI greed · Accepted Answer · 2009-10-13 07:23:35Z

0

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

answered Oct 13, 2009 at 7:23

Ian Kemp - SO dead by AI greed

1

Collectives™ on Stack Overflow

Problem in Creating Table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related