Union with Dummy data
580410Nov 14 2007 — edited Nov 16 2007I have a table with 4 fields: customer, item, color, num_purchased
There are 3 items: shirt, shoes and jeans.
And for each item, there are only 2 colors: blue and black
select * from purchase_table
CUSTOMER ITEM COLOR NUM_PURCHASED
john shirt blue 2
john shoes black 1
mary jeans black 1
mary jeans blue 1
mary shirt blue 3
mary shoes blue 2
I want to do add dummy data (0s) to my query using union
select * from purchase_table
union ...
so that my result set is ...
CUSTOMER ITEM COLOR NUM_PURCHASED
john shirt black 0
john shirt blue 2
john shoes black 1
john shoes blue 0
mary jeans black 1
mary jeans blue 1
mary shirt black 0
mary shirt blue 3
mary shoes black 0
mary shoes blue 2
basically.. john purchased a blue shirt, so i want to add 0 black shirts purchased..
mary purchased black and blue jeans, so need to add dummy data
Note: I only want to add dummy data for the items each customer purchased, not for all the items.
Since john didn't purchase jeans, no dummy data for jeans.
Any help appreciated. Thanks.