FULL OUTER JOIN Question

From: Tyler Kellen <tyler(at)sleekcode(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: FULL OUTER JOIN Question
Date: 2005-10-14 18:02:39
Message-ID: 2005101413239.933110@devbox
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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=m.parent
WHERE
p.parent is null
GROUP BY p.id,p.trans_id,p.item_price,p.qty,m.item_price;

BAD RESULT:
mg=# select * from trans_item where id=20116;
id | trans_id | parent | qty | item_sku | item_price | item_tax
-------+----------+--------+-----+----------+------------+----------
20116 | 11216 | 20115 | 1 | 91400 | 0.50 | 0.0700
(1 row)

mg=# select * from trans where id=20116;
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=# select * from trans_item where trans_id=20116;
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=# select * from item_test where trans_id=20116;
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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tyler Kellen 2005-10-14 18:06:36 FULL OUTER JOIN Question (mistake)
Previous Message george young 2005-10-14 16:19:21 Re: owner of data type "areas" appears to be invalid ?