Suppose that you have a small database for scheduling and
tracking tasks.
There are four tables:
PERSONS (name)
TASKS (name)
SCHEDULED (date, FK task, FK person)
EXECUTED (date, FK task, FK person)
so the relationships are:
a PERSON has zero or more EXECUTED
a PERSON has zero or more SCHEDULED
a TASK has zero or more EXECUTED
a TASK has zero or more SCHEDULED
now suppose that the database contains these rows:
PERSON ('john')
TASK ('mowing')
TASK ('digging')
TASK ('pruning')
SCHEDULED ('sunday', 'mowing', 'john')
SCHEDULED ('monday', 'digging', 'john')
EXECUTED ('tuesday', 'mowing', 'john')
EXECUTED ('wednesday', 'pruning', 'john')
I need to build a query that produces a list of rows,
in the EXECUTED table, with added the scheduled.day
*if exists*. That is, something like:
executed.day task.name person.name scheduled.day
-----------------------------------------------------
'tuesday' 'mowing' 'john' 'sunday'
'wednesday' 'pruning' 'john' (NULL)
I realize that the problem would be trivial if SCHEDULED and
EXECUTED were a single table. However, I find myself in a
situation like the one described, and altering the database
structure is not possible.
Is there to build such a query in this situation? So far,
I haven't been able to.
Thanks,
Giovanni