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.gr...@oracle.com>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
>
>
