Re: An example for WITH QUERY

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