List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:July 12 2012 6:19pm
Subject:Re: Finding Rows With Common Items
View as plain text  
SQL>  select * from orddd;

   ORDERID     PRODID
---------- ----------
         2          5
         1          3
         1          2
         2          7
         1          5

SQL> select prodid,count(*) from orddd group by PRODID having count(*) > 1;

    PRODID   COUNT(*)
---------- ----------
         5          2



On Thu, Jul 12, 2012 at 11:42 PM, Shawn Green <shawn.l.green@stripped>wrote:

> 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
>
>

Thread
Finding Rows With Common ItemsJohn Nichel12 Jul
  • Re: Finding Rows With Common ItemsShawn Green12 Jul
    • Re: Finding Rows With Common ItemsAnanda Kumar12 Jul