Re: a query problem
| От | venkatrao.b@tcs.com |
|---|---|
| Тема | Re: a query problem |
| Дата | |
| Msg-id | OF0878C797.4A9ACA30-ON652576DD.00184E83-652576DD.00189E1B@tcs.com обсуждение исходный текст |
| Ответ на | Re: a query problem (Michael Wood <esiotrot@gmail.com>) |
| Список | pgsql-novice |
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@gmail.com> |
| To: | Giovanni Gigante <giov@cidoc.iuav.it> |
| Cc: | pgsql-novice@postgresql.org |
| Date: | 03/05/2010 02:46 AM |
| Subject: | Re: [NOVICE] a query problem |
| Sent by: | pgsql-novice-owner@postgresql.org |
On 4 March 2010 21:15, Giovanni Gigante <giov@cidoc.iuav.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@gmail.com>
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.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
В списке pgsql-novice по дате отправления:
Предыдущее
От: "Greg Cocks"Дата:
Сообщение: SUM - Seeking experiences 'accessing' Microsoft Active Directory credentials from PostgreSQL, in conjunction with the sys admin / IT...