Page 1 of 1

select from orders where product purchased by same customer

Posted: Tue Mar 03, 2009 3:30 pm
by Luke
I need to do a query on my orders table for anybody who bought a specific product more than once. I understand how to do the joins on the cart items and order tables, but I am not so sure how I would only select those who have bought a product more than once.

For instance, let's assume my table looks like this, and the specific product is "book".

Code: Select all

orderid         buyerID         orderdate           item
            
1           1               2/15/2008           rock
2           2               2/15/2008           cheese
3           3               2/19/2005           book
4           3               3/5/2008            book
5           4               2/15/2008           book
6           5               9/21/2007           milk
7           6               7/14/2007           book
8           6               4/23/2006           book
9           6               6/6/2003            chapstick
With the given data, I'd want my query to return rows 3,4,7 & 8. I'd want these because the same person bought "book" multiple times. How would I go about this?

Re: select from orders where product purchased by same customer

Posted: Tue Mar 03, 2009 4:34 pm
by papa
Do I missunderstand something ?

select where buyerID = 3 and item = book

Re: select from orders where product purchased by same customer

Posted: Tue Mar 03, 2009 5:05 pm
by VladSun
[sql]SELECT    orders.*FROM     ordersINNER JOIN    orders AS repeated_orders    ON        orders.item = repeated_orders.item        AND       orders.buyerID = repeated_orders.buyerID       AND        orders.orderID != repeated_orders.orderID[/sql]

Re: select from orders where product purchased by same customer

Posted: Tue Mar 03, 2009 5:30 pm
by Luke
papa wrote:Do I missunderstand something ?

select where buyerID = 3 and item = book
yep you are missing something. I want to find all records where somebody has bought "book" more than once. So... I can't specify buyerID, because that's what I'm LOOKING for.

Vladsun... thanks I'll give it a try...

Re: select from orders where product purchased by same customer

Posted: Tue Mar 03, 2009 8:26 pm
by josh
Select * from A where A.fk IN ( select `customer_id`, count( `id` ) as c from B where c > 1 )

( didnt check for correctness but I've used subqueries for this, the join approach looks interesting though. )

Re: select from orders where product purchased by same customer

Posted: Wed Mar 04, 2009 2:17 am
by Weirdan

Code: Select all

 
SELECT buyerID FROM orders
WHERE item="book"
GROUP BY buyerID
HAVING count(*) > 1
 

Re: select from orders where product purchased by same customer

Posted: Wed Mar 04, 2009 2:29 am
by VladSun
josh wrote:Select * from A where A.fk IN ( select `customer_id`, count( `id` ) as c from B where c > 1 )

( didnt check for correctness but I've used subqueries for this, the join approach looks interesting though. )
Because you want to reffer to aggregate function in a WHERE clause you have to use GROUP BY and HAVING clauses. And GROUP BY will require a filesort which is slow and no indexies could be used.

Re: select from orders where product purchased by same customer

Posted: Wed Mar 04, 2009 2:37 am
by VladSun
Weirdan wrote:

Code: Select all

 
SELECT buyerID FROM orders
WHERE item="book"
GROUP BY buyerID
HAVING count(*) > 1
 
It's OK but you still have to show the whole rows which will transform your query into josh's one :)
Luke wrote:I'd want my query to return rows 3,4,7 & 8.

Re: select from orders where product purchased by same customer

Posted: Sun Mar 08, 2009 11:31 am
by Weirdan
VladSun wrote: It's OK but you still have to show the whole rows which will transform your query into josh's one :)
Luke wrote:I'd want my query to return rows 3,4,7 & 8.
Missed that part.

Re: select from orders where product purchased by same customer

Posted: Sun Mar 08, 2009 2:39 pm
by josh
But it does use indexes.

EXPLAIN SELECT *
FROM a
WHERE a
IN (

SELECT b
FROM b
)
( with index on table a column a and table b column b )
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a index NULL a 4 NULL 10352 Using where; Using index
2 DEPENDENT SUBQUERY b index_subquery b b 4 func 1 Using index

Without any indexes
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL NULL NULL NULL NULL 10352 Using where
2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 351 Using where

Even if you do::
EXPLAIN SELECT *
FROM a
WHERE a
IN ( 500, 501, 502 )
It tells me it is using the index.

My previous query was incorrect though since you can only select 1 column for a subquery in an IN clause.

EXPLAIN SELECT a, count( a ) AS count
FROM a
GROUP BY a
ORDER BY `count` DESC

10352 Using index; Using temporary; Using filesort ( this inner query does examine all rows though, as would be expected, does the having method overcome this, I couldn't imagine how )

Re: select from orders where product purchased by same customer

Posted: Mon Mar 09, 2009 4:47 am
by VladSun
josh wrote:But it does use indexes.
Yes, your query does use indexies.
But if you have to rewrite this query according to the requirements in the OP (by including the Weirdan's query), I don't think it will use indexies (i.e. it will use filesort for at least one step).
Please, write the full query needed by the OP using your subquery approach.

Re: select from orders where product purchased by same customer

Posted: Mon Mar 09, 2009 6:04 am
by josh
VladSun wrote:Please, write the full query needed by the OP using your subquery approach.
Dunno. I'm not trying to argue. You could always issue 2 separate queries. Build an array of ids. use implode() and generate the 2nd query. Not saying that would scale up though, I don't think a filesort is the end of the world.

Re: select from orders where product purchased by same customer

Posted: Mon Mar 09, 2009 6:18 am
by Benjamin
I think this is the best solution guys...

Code: Select all

 
SELECT
  o.buyerID
FROM
  orders o
WHERE
   o.item = 'book'
  AND EXISTS(SELECT o2.orderid FROM orders o2 WHERE o2.buyerID = o.buyerID AND o2.orderid != o.orderid AND o2.item = o.item LIMIT 1)
GROUP BY
  o.buyerID
ORDER BY
  o.orderdate ASC
 

Re: select from orders where product purchased by same customer

Posted: Mon Mar 09, 2009 8:22 am
by VladSun
Some benchmarks:

[sql]CREATE TABLE  `orders` (  `orderId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,  `buyerId` int(10) UNSIGNED NOT NULL,  `item` int(10) UNSIGNED NOT NULL,  PRIMARY KEY  (`orderId`),  KEY `IX_buyerId` (`buyerId`),  KEY `IX_item` (`item`),  KEY `IX_buyerId_item` (`buyerId`,`item`),  KEY `IX_All` (`orderId`,`buyerId`,`item`)) ENGINE=MyISAM AUTO_INCREMENT=5424766 DEFAULT CHARSET=utf8;[/sql]

[sql]SELECT count(*) FROM orders;[/sql]
420016
My query (note that I had a bug in my previous query, that's why I put DISTINCT. Otherwise in case there are more than 2 identical orders, the result will be doubled):
[sql]SELECT SQL_NO_CACHE    DISTINCT orders.*FROM    ordersINNER JOIN    orders AS repeated_orders    ON       orders.item = repeated_orders.item       AND       orders.buyerID = repeated_orders.buyerID       AND       orders.orderID <> repeated_orders.orderIDWHERE  orders.item = 80[/sql]
46 rows fetched in 0.0020s (0.0031s)
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","orders","ref","IX_buyerId,IX_item,IX_buyerId_item","IX_item","4","const",106,"Using temporary"
1,"SIMPLE","repeated_orders","ref","IX_buyerId,IX_item,IX_buyerId_item","IX_buyerId_item","8","tt.orders.buyerId,const",1,"Using where; Distinct"
astions's query
[sql]SELECT SQL_NO_CACHE  orders.*FROM  ordersWHERE  orders.item = 80  AND  EXISTS(    SELECT SQL_NO_CACHE      repeated_orders.orderid    FROM      orders AS repeated_orders    WHERE      repeated_orders.buyerID = orders.buyerID      AND      repeated_orders.orderid != orders.orderid      AND      repeated_orders.item = orders.item    LIMIT      1  )[/sql]
46 rows fetched in 0.0020s (0.0031s)
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","orders","ref","IX_item","IX_item","4","const",106,"Using where"
2,"DEPENDENT SUBQUERY","repeated_orders","ref","IX_buyerId,IX_item,IX_buyerId_item","IX_buyerId_item","8","tt.orders.buyerId,const",1,"Using where"
josh-Weirdan query
[sql]SELECT SQL_NO_CACHE  orders.*FROM  ordersWHERE  buyerId IN  (    SELECT SQL_NO_CACHE      buyerId    FROM      orders    WHERE      item=80    GROUP BY      buyerID    HAVING      count(*) > 1  )  AND    item=80[/sql]
46 rows fetched in 0.0025s (0.1714s)
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","orders","ref","IX_item","IX_item","4","const",106,"Using where"
2,"DEPENDENT SUBQUERY","orders","ref","IX_item","IX_item","4","const",106,"Using where; Using temporary; Using filesort"
If you feel like it's not what you meant, please tell me.