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 beable to recreate the math
using just the trans_item rows as well. Sometimes an modifier is flagged tobe 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 functionscorrectly.

I've removed a lot of the tax and discounting math to make the query moresimple 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) asparent_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

Tyler Kellen <tyler@sleekcode.net> writes:
> I have a question about a full outer join returning duplicate rows.

Why do you think they are duplicate? The GROUP BY includes many columns
that you can't see directly in the output ...

Also, you did not show us the actual input data (where's the rows with
trans_item.parent=20116?)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster