On 7/12/2012 1:49 PM, John Nichel wrote:
Hi all,
Lets say I have a table with two colums: 'orderid' and 'productid'. This
table contains line items for purchases in a store where a purchase could
be 1 or more line items (1 or more rows). A typical order may look like
this:
orderid | productid
12345 | 9876
12345 | 6789
12345 | 7698
Is there a simple way to query the table to pull orders which have 2 or
more products in common? For example, if order 12345 has 5 line items and
order 12348 has 7 line items, I would like the query to return these two
orders if they have products 9876 and 6789 in common, and I would also
like it to return orders 23456, 65432 and 34567 where they have 8796, 6789
and 4456 in common. I hope I'm explaining this well...I know what I'm
trying to accomplish in my head, but we all know that doesn't always
translate too well in an email. :) For the record, this is a MySQL
4.1.22 db. TIA
Basically you want an index on (productid, orderid) on the main table to
do the lookup faster. As you may want to do this query only once, make a
list of all of the products that have particpated in more than one order
then periodically update this list.
One way to create this list is with the following query:
CREATE TABLE multipleordprods (key (productid,ordercount),
key(ordercount, productid))
SELECT productid, count(orderid) ordercount
FROM <your table name here>
GROUP BY productid HAVING ordercount >1;
Now we have a list of all productid values that participated in more
than 1 order. You can now sort this table by number of orders or by
product. You can join this table back to your original table to get a
list of the orderid for any one product. This summary table is the key
to drilling into your data.
You can also add more columns to this table or create other summary
tables using combinations of time or price or any other dimensions you
want to use to slice and dice your data. This is the core principal to
designing a data warehouse for online analytical processing (OLAP).
Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
