Is it possible to do a bitwise SUM()?
IE:
user_id key
1 1 (001)
1 2 (010)
2 4 (100)
3 2 (010)
SELECT user_id SUM()… FROM table GROUP BY user_id;
user_id SUM()
1 3 (011)
2 4 (100)
3 2 (010)
anything is possible with SQL 
could you please explain a bit more about what you’re doing?
also, please do a SHOW CREATE TABLE for your table
Inheriting someone else’s table lol
the schema is:
products
product_id int(2) PRIMARY,
… (unnecessary fields)
product_purchase_type int(2) (1-19, plus several NULL entries. NOT an index.)
product_key tinyint(1) (effectively an enum(1,2,3,4), bitwise 3-bit key. This field is -not- unique.)
purchases
purchase_id bigint(9) PRIMARY AUTO_INCREMENT (sigh. horrible design)
purchase_type_id int(2) (FK: products.product_purchase_type)
user_id bigint(6) (FK: users.user_id)
Given a specific user_id value, i’m trying to determine all the products purchased. left joining purchases and products and then grouping on user_id gives me the groups… but SUM of 3 and 2 and 1 and 4 both = 5…and given the prior programmer’s sloppyness, i cant guarantee there isnt a 3 and 2 for the same user in the table.
So how does one determine the product a user purchased? The puchase table makes little sense considering the column product_purchase_type inside products is not unique. Am I missing something? I don’t quite understand the decision not to use product_id as the foreign key and that is probably a significant part to solving this with the given tables.
Right products.product_purchase_type is not mysql-enforced-unique (as it allows Null), but among values specified in the table, it is ‘unique’; product_key is not.
Think of it this way: There are 3 products. (1,2,4, bitwise).
A person could go to the website, and purchase products 1&2 as a bundle. This would have product_purchase_type of say… 6 (which comes from the code as the product ID passed into clickbank’s service for payment), and product_key of 3 (001 + 010).
However, if said person clicked on a link in the email newsletter, they still might be buying products 1&2 as a bundle. This time, product_purchase_type of 9, and product_key of 3.
Again, not my table design, i’m just inheriting it.