X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0379DD9736 for ; Fri, 14 Oct 2005 15:02:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55398-09 for ; Fri, 14 Oct 2005 18:02:36 +0000 (GMT) Received: from apollo.cloudnet.com (apollo.cloudnet.com [209.188.96.28]) by svr1.postgresql.org (Postfix) with ESMTP id DD269D94FD for ; Fri, 14 Oct 2005 15:02:35 -0300 (ADT) Received: from apollo.cloudnet.com (localhost.cloudnet.com [127.0.0.1]) by apollo.cloudnet.com (Postfix) with ESMTP id AF2F6D6E895 for ; Fri, 14 Oct 2005 13:02:35 -0500 (CDT) Received: from devbox (meetinggrounds.com [209.188.103.253]) by apollo.cloudnet.com (Postfix) with SMTP id 4FF0BD6E871 for ; Fri, 14 Oct 2005 13:02:35 -0500 (CDT) From: Tyler Kellen To: X-Mailer: PocoMail 3.4 (2130) - Licensed Version X-URL: http://www.sleekcode.net Reply-To: tyler@sleekcode.net Date: Fri, 14 Oct 2005 13:02:39 -0500 Message-ID: <2005101413239.933110@devbox> Subject: FULL OUTER JOIN Question Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="34128146-POCO-36338747" X-Virus-Scanned: ClamAV using ClamSMTP X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200510/170 X-Sequence-Number: 23023 This is a multipart message in MIME format --34128146-POCO-36338747 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a question about a full outer join returning duplicate rows. I have one table that stores a record for each transaction with totals: CREATE TABLE trans ( id serial PRIMARY KEY, stamp timestamp DEFAULT now(), trans_type_id int NOT NULL REFERENCES trans_type(id), subtotal numeric(6,2), tax numeric(6,2), total_cash numeric(6,2), total_credit numeric(6,2), total_check numeric(6,2), total_gift numeric(6,2) ); I also have a table that stores each item sold: CREATE TABLE trans_item ( id serial PRIMARY KEY, trans_id int NOT NULL REFERENCES trans(id), parent int REFERENCES trans_item(id), qty int NOT NULL DEFAULT 1, item_sku text NOT NULL CHECK(item_sku <> '') REFERENCES item(sku), item_price numeric(5,2), item_tax numeric(4,4) ); Each item can be 'modified' by another item (only one tier of this). This is handled with the 'parent' column. If I sell Item A with an extra, say Item B it would look something like this= (assume the trans_item_id sequence is starting at 1) INSERT INTO trans_item (trans_id,qty,item_sku,item_price,item_tax) VALUES= (1,1,'itema',5.00,0.07); INSERT INTO trans_item (trans_id,parent,item_sku,item_price,item_tax) VALUES= (1,1,'itemb',1.00,0.07); My 'trans' table records the totals for this transaction but I need to be= able to recreate the math using just the trans_item rows as well. Sometimes an modifier is flagged to= be sold as a free extra to the parent. When I have items that are free my query returns two rows,= one with the total and modifier totals and one without. If I add a price to the modifier that was supposed to be free it functions= correctly. I've removed a lot of the tax and discounting math to make the query more= simple to look at, it fails the same way with or without it. here is my query: CREATE VIEW item_test AS SELECT p.id, p.trans_id, (p.item_price*p.qty)+COALESCE(sum(m.item_price)*p.qty,0) as= parent_subtotal, COALESCE(sum(m.item_price)*p.qty,0) as mod_subtotal FROM trans_item p FULL OUTER JOIN trans_item m ON p.id=3Dm.parent WHERE p.parent is null GROUP BY p.id,p.trans_id,p.item_price,p.qty,m.item_price; BAD RESULT: mg=3D# select * from trans_item where id=3D20116; id | trans_id | parent | qty | item_sku | item_price | item_tax -------+----------+--------+-----+----------+------------+---------- 20116 | 11216 | 20115 | 1 | 91400 | 0.50 | 0.0700 (1 row) mg=3D# select * from trans where id=3D20116; id | stamp | trans_type_id | subtotal | tax |= total_cash | total_credit | total_check | total_gift -------+-------------------------+---------------+----------+------+--------= ----+--------------+-------------+------------ 20116 | 2005-10-14 12:58:13.671 | 1 | 2.25 | 0.16 | = 5.00 | 0.00 | 0.00 | 0.00 (1 row) mg=3D# select * from trans_item where trans_id=3D20116; id | trans_id | parent | qty | item_sku | item_price | item_tax -------+----------+--------+-----+----------+------------+---------- 36437 | 20116 | | 1 | 10000 | 1.75 | 0.0700 36438 | 20116 | 36437 | 1 | 91200 | 0.50 | 0.0700 36439 | 20116 | 36437 | 1 | 90100 | 0.00 | 0.0700 (3 rows) mg=3D# select * from item_test where trans_id=3D20116; id | trans_id | parent_subtotal | mod_subtotal -------+----------+-----------------+-------------- 36437 | 20116 | 1.75 | 0.00 36437 | 20116 | 2.25 | 0.50 (2 rows) ^ This should only return the second row. What gives? If anyone has the time to look at this I would greatly appreciate it! Best, Tyler Kellen --34128146-POCO-36338747 Content-Type: text/html Content-Transfer-Encoding: Quoted-Printable
I have a question about a= full outer join returning duplicate rows.
 
I have one table= that stores a record for each transaction with totals:
CREATE TABLE= trans
(
  id=             = 60;serial PRIMARY KEY,
  stamp=           timestamp= DEFAULT now(),
  trans_type_id=   int NOT NULL REFERENCES trans_type(id),
  subtotal=        numeric(6,2),
  tax=             num= eric(6,2),
  total_cash=      numeric(6,2),
  total_credit=    numeric(6,2),
  total_check=     numeric(6,2),
  total_gift=      numeric(6,2)
);
 
I also have a table= that stores each item sold:
CREATE TABLE= trans_item
(
  id=           serial PRIMARY= KEY,
  trans_id=     int NOT NULL REFERENCES trans(id),
  parent=       int REFERENCES= trans_item(id),
  qty=          int NOT NULL DEFAULT= 1,
  item_sku=     text NOT NULL CHECK(item_sku <> '')= REFERENCES item(sku),
  item_price=   numeric(5,2),
  item_tax=     numeric(4,4)
);
 
Each item can be= 'modified' by another item (only one tier of this).
This is handled= with the 'parent' column.
 
If I sell Item A= with an extra, say Item B it would look something like this (assume the= trans_item_id sequence is starting at 1)
 
INSERT INTO= trans_item (trans_id,qty,item_sku,item_price,item_tax) VALUES= (1,1,'itema',5.00,0.07);
INSERT INTO= trans_item (trans_id,parent,item_sku,item_price,item_tax) VALUES= (1,1,'itemb',1.00,0.07);
 
My 'trans' table= records the totals for this transaction but I need to be able to recreate= the math
using just the= trans_item rows as well.  Sometimes an modifier is flagged to be sold= as a free extra
to the parent.=  When I have items that are free my query returns two rows, one with= the total and modifier totals and one without.
If I add a price to= the modifier that was supposed to be free it functions= correctly.
 
I've removed a lot= of the tax and discounting math to make the query more simple to look at,= it fails the same way with or without it.
 
here is my= query:
CREATE VIEW= item_test AS
SELECT
  p.id,
  p.trans_id,
  (p.item_price*p.q= ty)+COALESCE(sum(m.item_price)*p.qty,0) as parent_subtotal,
  COALESCE(sum(m.it= em_price)*p.qty,0) as mod_subtotal
FROM
  trans_item= p
FULL OUTER= JOIN
  trans_item= m
ON
 p.id=3Dm.parent
WHERE
  p.parent is= null
GROUP BY= p.id,p.trans_id,p.item_price,p.qty,m.item_price;
 
 
BAD= RESULT:
mg=3D# select * from= trans_item where id=3D20116;
  id=   | trans_id | parent | qty | item_sku | item_price |= item_tax
-------+----------+--------+-= ----+----------+------------+----------
 20116 |=    11216 |  20115 |   1 | 91400=    |       0.50 |=   0.0700
(1= row)
 
mg=3D# select * from= trans where id=3D20116;
  id=   |          stamp=          | trans_type_id |= subtotal | tax  | total_cash | total_credit | total_check |= total_gift
-------+---------------------= ----+---------------+----------+------+------------+--------------+---------= ----+------------
 20116 |= 2005-10-14 12:58:13.671 |=             1 |=     2.25 | 0.16 |=       5.00 |=         0.00 |=        0.00 |=       0.00
(1= row)
 
mg=3D# select * from= trans_item where trans_id=3D20116;
  id=   | trans_id | parent | qty | item_sku | item_price |= item_tax
-------+----------+--------+-= ----+----------+------------+----------
 36437 |=    20116 |        |=   1 | 10000    |=       1.75 |   0.0700
 36438 |=    20116 |  36437 |   1 | 91200=    |       0.50 |=   0.0700
 36439 |=    20116 |  36437 |   1 | 90100=    |       0.00 |=   0.0700
(3= rows)
 
mg=3D# select * from= item_test where trans_id=3D20116;
  id=   | trans_id | parent_subtotal | mod_subtotal
-------+----------+----------= -------+--------------
 36437 |=    20116 |=            1.75 |=         0.00
 36437 |=    20116 |=            2.25 |=         0.50
(2= rows)
 
^ This should only= return the second row.  What gives?
 
If anyone has the= time to look at this I would greatly appreciate it!
 
Best,
Tyler= Kellen
--34128146-POCO-36338747--