Re: An example for WITH QUERY

[Thomas Kellerer <spam_eater@xxxxxxx>]

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 SELECTs into 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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general