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 | 1 | 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
