Populating a table with rows that don't exist
I have a table like this:
buyer product quantity
tom skirt 2
anna skirt 3
tom jeans 5
distinct(product) = skirt
and jeans
I need a table that inserts another column with quantity = 0
if the tuple <buyer, product>
does not exist for all possible products.
So the result will be:
buyer product quantity
tom skirt 2
anna skirt 3
tom jeans 5
anna jeans 0
It doesn't look very complicated, but I don't know how to do it.
UPDATE
I found another complication.
My product is actually defined by two fields: class and product. The product can be null, and I don't need to waste information when the product field is null (this happens with cross join now).
So, if I have this:
buyer class product quantity
tom clothes skirt 2
anna clothes skirt 3
tom clothes jeans 5
jim shoes NULL 7
I will need:
buyer class product quantity
tom clothes skirt 2
anna clothes skirt 3
tom clothes jeans 5
anna clothes jeans 0
jim shoes NULL 7
jim clothes skirt 0
jim clothes jeans 0
tom shoes NULL 0
anna shoes NULL 0
Thanks everyone, I know I'm over complicating things!
source to share
You can use cross join
to create all possible combinations of buyers and products. Then use left join
(or not exists
) to filter out the ones already listed in the table:
insert into table(buyer, product, quantity)
select b.buyer, p.product, 0
from (select distinct buyer from table) b cross join
(select distinct product p from table) p left join
table t
on t.buyer = b.buyer and t.product = p.product
where t.buyer is null;
EDIT:
If you want a query that returns all rows, you should use something very similar:
select b.buyer, p.product, coalesce(t.qty, 0) as qty
from (select distinct buyer from table) b cross join
(select distinct product p from table) p left join
table t
on t.buyer = b.buyer and t.product = p.product;
EDIT II:
If you have NULL
values for a customer and / or product, use NULL
safe comparisons:
select b.buyer, p.product, coalesce(t.qty, 0) as qty
from (select distinct buyer from table) b cross join
(select distinct product p from table) p left join
table t
on t.buyer is not distinct from b.buyer and
t.product is not distinct from p.product;
(As a side note: I really don't like the use distinct
of this construct. Why does Postgres (ANSI?) Give it such a complex name?)
source to share
@ Gordon's solution is almost complete, I edit like this:
declare @tb table (buyer varchar(150), product varchar(150), quantity int)
insert into @tb
values('tom','skirt',2),
('anna','skirt',3),
('tom','jeans',5)
select *
from @tb a
left join( select
distinct(product)
from @tb) b on a.product = a.product
select b.buyer, p.p, isnull(t.quantity,0)
from (select distinct buyer from @tb) b cross join
(select distinct product p from @tb) p left join
@tb t
on t.buyer = b.buyer and t.product = p.p
--where t.buyer is null
Try it.
source to share