Re: a query problem

On 4 March 2010 21:15, Giovanni Gigante <giov(at)cidoc(dot)iuav(dot)it> wrote:
>
> 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.

Ouch. I haven't thought this through, but maybe you could fix the
schema and then add views with triggers to make it look like above to
whatever it is that needs it to look like that?

> Is there to build such a query in this situation? So far,
> I haven't been able to.

How about this:

taskdb=> create table scheduled (day text, task text, person text);
CREATE TABLE
taskdb=> create table executed (day text, task text, person text);
CREATE TABLE
taskdb=> insert into scheduled values ('sunday', 'mowing', 'john'),
('monday', 'digging', 'john');
INSERT 0 2
taskdb=> insert into executed values ('tuesday', 'mowing', 'john'),
('wednesday', 'pruning', 'john');
INSERT 0 2
taskdb=> select * from scheduled;
day | task | person
--------+---------+--------
sunday | mowing | john
monday | digging | john
(2 rows)

taskdb=> select * from executed;
day | task | person
-----------+---------+--------
tuesday | mowing | john
wednesday | pruning | john
(2 rows)

taskdb=> select e.day, e.task, e.person, s.day from executed as e left
outer join scheduled as s on e.task = s.task and e.person = s.person;
day | task | person | day
-----------+---------+--------+--------
tuesday | mowing | john | sunday
wednesday | pruning | john |
(2 rows)

taskdb=>

--
Michael Wood <esiotrot(at)gmail(dot)com>