Обсуждение: An example for WITH QUERY

Hi!

I have 3 tables. I want to run a query that collect some data from
them, and join into one result table.

I show a little example, how to do this in another DB with script:

create temp table tmp_a as select id, name, sum(cost) cost from items
where... group by id, name with data;

create temp table tmp_b as select item_id, sum(price) price from bills
where... group by item_id with data;

create temp table tmp_c as select item_id, sum(price) price from
incoming_bills where... group by item_id with data;

select
  tmp_a.id, tmp_a.name, tmp_a.cost,
  tmp_b.price outgoing_price,
  tmp_c.price incoming_price
from tmp_a
left join tmp_b on (tmp_a.id = tmp_b.item_id)
left join tmp_c on (tmp_a.id = tmp_c.item_id)
order by name

This is very simple example. How I can write this in "WITH QUERY" form?

Thanks for every help!

dd

On Wed, Jun 22, 2011 at 1:35 PM, Durumdara <durumdara@gmail.com> wrote:
> Hi!
>
> I have 3 tables. I want to run a query that collect some data from
> them, and join into one result table.
>
> I show a little example, how to do this in another DB with script:
>
> create temp table tmp_a as select id, name, sum(cost) cost from items
> where... group by id, name with data;
>
> create temp table tmp_b as select item_id, sum(price) price from bills
> where... group by item_id with data;
>
> create temp table tmp_c as select item_id, sum(price) price from
> incoming_bills where... group by item_id with data;
>
> select
>  tmp_a.id, tmp_a.name, tmp_a.cost,
>  tmp_b.price outgoing_price,
>  tmp_c.price incoming_price
> from tmp_a
> left join tmp_b on (tmp_a.id = tmp_b.item_id)
> left join tmp_c on (tmp_a.id = tmp_c.item_id)
> order by name
>
> This is very simple example. How I can write this in "WITH QUERY" form?
>
> Thanks for every help!
>
> dd
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

My understanding is that "WITH" queries are to facilitate the
capturing of a "dynamic" relation and enabling it's usage multiple
times within the query without having to have this "dynamic" result
set or relation regenerated for each use within the larger query.
This would be useful in situations where one had to create a temporary
table then make use of this table multiple times in a complex query.

In your case I see no need to use WITH queries (but I could be wrong).
Perhaps the expert documentation may help see
"http://www.postgresql.org/docs/9.0/interactive/queries-with.html".

Allan.

Durumdara, 22.06.2011 12:35:
> Hi!
>
> I have 3 tables. I want to run a query that collect some data from
> them, and join into one result table.
>
> I show a little example, how to do this in another DB with script:
>

with tmp_a as (
   select id, name, sum(cost) cost
   from items
   ...
),
temp_b as (
   select item_id, sum(price) price
   from bills
),
temp_c as (
   select item_id, sum(price) price
   from incoming_bills
   where... group by item_id with data
)
select
   tmp_a.id, tmp_a.name, tmp_a.cost,
   tmp_b.price outgoing_price,
   tmp_c.price incoming_price
from tmp_a
left join tmp_b on (tmp_a.id = tmp_b.item_id)
left join tmp_c on (tmp_a.id = tmp_c.item_id)
order by name

But a with is not really necessary here (although I personally find it easier to read) because you can simply put those
SELECTsinto the from clause: 

select
   tmp_a.id, tmp_a.name, tmp_a.cost,
   tmp_b.price outgoing_price,
   tmp_c.price incoming_price
from (
    select id, name, sum(cost) cost
    from items
) temp_a
left join (
   select item_id, sum(price) price
   from bills
) tmp_b on (tmp_a.id = tmp_b.item_id)
left join (
   select item_id, sum(price) price
   from incoming_bills
   where... group by item_id with data
) tmp_c on (tmp_a.id = tmp_c.item_id)
order by name