From: Shawn Green
Date: July 12 2012 6:12pm
Subject: Re: Finding Rows With Common Items
List-Archive: http://lists.mysql.com/mysql/227812
Message-Id: <4FFF138B.5060400@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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