Re: a query problem

left outer join is definitly a good solution, or else you can try this
too..(may be small improvement in performance, in case there are lots of
records in sheduled table, which you dont need to join)

select e.day,
e.task,
e.person,
(select s.day from scheduled as s where e.task = s.task and
e.person = s.person )as s.day
from executed as e
;

venkat

From:
Michael Wood <esiotrot(at)gmail(dot)com>
To:
Giovanni Gigante <giov(at)cidoc(dot)iuav(dot)it>
Cc:
pgsql-novice(at)postgresql(dot)org
Date:
03/05/2010 02:46 AM
Subject:
Re: [NOVICE] a query problem
Sent by:
pgsql-novice-owner(at)postgresql(dot)org

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>

--
Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you