Thread: 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