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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
