starlion — 2010-12-01T14:32:51-05:00 — #1
Is it possible to do a bitwise SUM()?
1 1 (001)
1 2 (010)
2 4 (100)
3 2 (010)
SELECT user_id SUM()... FROM table GROUP BY user_id;
1 3 (011)
2 4 (100)
3 2 (010)
r937 — 2010-12-01T14:49:05-05:00 — #2
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
starlion — 2010-12-01T15:02:04-05:00 — #3
Inheriting someone else's table lol
the schema is:
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.)
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.
oddz — 2010-12-02T00:38:05-05:00 — #4
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.
starlion — 2010-12-02T14:01:26-05:00 — #5
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.